Tuesday, November 20, 2007
Comma to table type conversion in oracle
WITH mytable AS ( SELECT '1,2,3,44,5,6,7,8,9,10,11,2,3,4,555' myfield FROM dual )SELECT LPAD( SUBSTR( ',' mytable.myfield ',' , INSTR( ',' mytable.myfield ',', ',', 1, n ) + 1 , INSTR( ',' mytable.myfield ',', ',', 1, n + 1 ) - INSTR( ',' mytable.myfield ',', ',', 1, n ) - 1 ) , 5 ) FROM mytable , ( SELECT level n FROM mytable CONNECT BY level <= LENGTH( mytable.myfield ) - LENGTH( REPLACE( mytable.myfield, ',' ) ) + 1 ) ORDER BY n;
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment