Tuesday, June 14, 2011

oracle sql query example for comma delimited single row to multiple row separated by comma delimiter

oracle sql query example for comma delimited single row to multiple row separated by comma delimiter
------------------------------------------------------------------------------


SQL> var mylist varchar2(100)
SQL> exec :mylist := '5,11,13,22,23,31,44,45'

PL/SQL procedure successfully completed.

SQL> select substr(:mylist,
2 instr(','||:mylist||',', ',', 1, rn),
3 instr(','||:mylist||',', ',', 1, rn+1)
4 - instr(','||:mylist||',', ',', 1, rn) - 1) value
5 from ( select rownum rn from dual
6 connect by level
7 <= length(:mylist)-length(replace(:mylist,',',''))+1
8 )
9 /
VALUE
-----------------------------------------------------------
5
11
13
22
23
31
44
45

No comments: