To Convert Comma Delimited String to Result Set

To Convert Comma Delimited Strings to Result Set

We have seen Query to Convert Comma Delimited Strings to Result Set previously. Here the same has been implemented using pipelined function. It can be reused when there is a need to use comma to table conversion logic in more than one place. CREATE FUNCTION FN_CONV_COMMA_TO_RESULTSET (IN_COMMA_DELIMTED_STRING VARCHAR2) RETURN TYP_COMMA_RSLT_SET PIPELINED AS V_TYP_COMMA_RSLT_SET TYP_COMMA_RSLT_SET; BEGIN 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 STRINGS BULK COLLECT INTO V_TYP_COMMA_RSLT_SET FROM (SELECT ',' || IN_COMMA_DELIMTED_STRING || ',' COMMA_DELIMTED_STRING, LENGTH (IN_COMMA_DELIMTED_STRING || ',') - LENGTH (REPLACE (IN_COMMA_DELIMTED_STRING || ',', ',', '' ) ) NO_OF_STRINGS FROM DUAL) CONNECT BY LEVEL <= NO_OF_STRINGS; FOR STRING_SET IN 1 .. V_TYP_COMMA_RSLT_SET.COUNT LOOP PIPE ROW (V_TYP_COMMA_RSLT_SET (STRING_SET)); END LOOP; END FN_CONV_COMMA_TO_RESULTSET; /

SQL> CREATE TYPE TYP_COMMA_RSLT_SET IS TABLE OF VARCHAR2(255) / Type created. SQL> CREATE FUNCTION FN_CONV_COMMA_TO_RESULTSET (IN_COMMA_DELIMTED_STRING VARCHAR2) 2 RETURN TYP_COMMA_RSLT_SET PIPELINED 3 AS 4 V_TYP_COMMA_RSLT_SET TYP_COMMA_RSLT_SET; 5 BEGIN 6 SELECT SUBSTR (COMMA_DELIMTED_STRING, 7 INSTR (COMMA_DELIMTED_STRING, ',', 1, LEVEL) + 1, 8 INSTR (COMMA_DELIMTED_STRING, ',', 1, LEVEL + 1) 9 INSTR (COMMA_DELIMTED_STRING, ',', 1, LEVEL) 10 - 1 11 STRINGS 12 BULK COLLECT INTO V_TYP_COMMA_RSLT_SET 13 FROM (SELECT ',' 14 || IN_COMMA_DELIMTED_STRING 15 || ',' COMMA_DELIMTED_STRING, 16 LENGTH (IN_COMMA_DELIMTED_STRING || ',') 17 - LENGTH (REPLACE (IN_COMMA_DELIMTED_STRING || ',', 18 ',', 19 '' 20 ) 21 ) NO_OF_STRINGS 22 FROM DUAL) 23 CONNECT BY LEVEL <= NO_OF_STRINGS; 24 FOR STRING_SET IN 1 .. V_TYP_COMMA_RSLT_SET.COUNT 25 LOOP 26 PIPE ROW (V_TYP_COMMA_RSLT_SET (STRING_SET)); 27 END LOOP; 28 END FN_CONV_COMMA_TO_RESULTSET; 29 / Function created. SQL> COLUMN strings format a30 SQL> SELECT 2 COLUMN_VALUE AS STRINGS 3 FROM 4 TABLE( 5 FN_CONV_COMMA_TO_RESULTSET('Scott,Sachin-Tendulkar,Michael,Amma,pipelined-function') 6 ); STRINGS ------------------------------ Scott Sachin-Tendulkar Michael Amma pipelined-function

This function can be reused. Another way is we can create this function and type as system user and grant execute privilege to other oracle users... you feedback is appreciated... :)

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.