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;

No comments: