Query to Convert Comma Delimited String to Result Set

Query to Convert Comma Delimited String to Result Set

Yes we can convert comma delimited string to result set. Although we have DBMS_UTILITY.COMMA_TO_TABLE, it is not accepting special characters... so, i have tried query to get it done... eg: to convert "Scott,Sachin-Tendulkar,Michael,Amma" as Result ------------- Scott Sachin Michael Amma Here is the query SELECT SUBSTR ( COMMA_DELIMTED_STRING , INSTR ( COMMA_DELIMTED_STRING , ',', 1, LEVEL) + 1, INSTR ( COMMA_DELIMTED_STRING , ',', 1, LEVEL + 1) - INSTR ( COMMA_DELIMTED_STRING , ',', 1, LEVEL) - 1 ) AS STRINGS FROM (SELECT ',' || COMMA_DELIMTED_STRING || ',' AS COMMA_DELIMTED_STRING , LENGTH ( COMMA_DELIMTED_STRING || ',') - LENGTH (REPLACE ( COMMA_DELIMTED_STRING || ',', ',', '')) AS NO_OF_STRINGS FROM (SELECT 'Scott,Sachin-Tendulkar,Michael,Amma' AS COMMA_DELIMTED_STRING FROM DUAL)) CONNECT BY LEVEL <= NO_OF_STRINGS /

SQL> COLUMN strings format a30 SQL> SELECT SUBSTR ( COMMA_DELIMTED_STRING , 2 INSTR ( COMMA_DELIMTED_STRING , ',', 1, LEVEL) + 1, 3 INSTR ( COMMA_DELIMTED_STRING , ',', 1, LEVEL + 1) 4 - INSTR ( COMMA_DELIMTED_STRING , ',', 1, LEVEL) 5 - 1 6 ) AS STRINGS 7 FROM (SELECT ',' || COMMA_DELIMTED_STRING || ',' AS COMMA_DELIMTED_STRING , 8 LENGTH ( COMMA_DELIMTED_STRING || ',') 9 - LENGTH (REPLACE ( COMMA_DELIMTED_STRING || ',', ',', '')) 10 AS NO_OF_STRINGS 11 FROM (SELECT 'Scott,Sachin-Tendulkar,Michael,Amma' AS COMMA_DELIMTED_STRING 12 FROM DUAL)) 13 CONNECT BY LEVEL <= NO_OF_STRINGS 14 / STRINGS ------------------------------ Scott Sachin-Tendulkar Michael Amma

In the next page the same functionality is achieved using pipelined function, which will be reusable if comma to table conversion is to be done in more than one place.