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
WWSS
1 day ago
No comments:
Post a Comment