How to make result set from ('1','2','3') ?

Question

Hello! I have a question, how can i make a result set making only list of values. For example i have such values : ('1','2','3') And i want to make a sql that returns such table:

1
2
3

Thanks.

[Edit]

Sorry for wrong question. Actually list not containing integers, but it contains strings. I am currently need like ('aa','bb,'cc').

[/Edit]

Answer 1

The easiest way is to abuse a table that is guaranteed to have enough rows.

-- for Oracle
select rownum from tab where rownum < 4;

If that is not possible, check out Oracle Row Generator Techniques.

I like this one (requires 10g):

select integer_value
from   dual
where  1=2
model
  dimension by ( 0 as key )
  measures     ( 0 as integer_value )
  rules upsert ( integer_value[ for key from 1 to 10 increment 1 ] = cv(key) )
;

Answer 2

One trick I've used in various database systems (not just SQL databases) is actually to have a table which just contains the first 100 or 1000 integers. Such a table is very easy to create programatically, and your query then becomes:

SELECT value FROM numbers WHERE value < 4 ORDER BY value

You can use the table for lots of similar purposes.

Answer 3

If you want to write a SQL statement which will take a comma separate list and generate an arbitrary number of actually rows the only real way would be to use a table function, which calls a PL/SQL function which splits the input string and returns the elements as separate rows.

Check out this link for an intro to table-functions.

Alternatively, if you can construct the SQL statement programmatically in your client you can do:

SELECT 'aa' FROM DUAL
UNION
SELECT 'bb' FROM DUAL
UNION
SELECT 'cc' FROM DUAL

Answer 4

The best way I've found is using XML.

SELECT items.extract('/l/text()').getStringVal() item
FROM TABLE(xmlSequence(
        EXTRACT(XMLType(''||
        REPLACE('aa,bb,cc',',','')||'')
          ,'/all/l'))) items;

Wish I could take credit but alas : http://pbarut.blogspot.com/2006/10/binding-list-variable.html.

Basically what it does is convert the list to an xmldocument then parse it back out.