Split function in oracle to comma separated values with automatic sequence

Question

Need Split function which will take two parameters, string to split and delimiter to split the string and return a table with columns Id and Data.And how to call Split function which will return a table with columns Id and Data. Id column will contain sequence and data column will contain data of the string. Eg.

SELECT*FROM Split('A,B,C,D',',') 

Result Should be in below format:

|Id | Data  --   ---- |1  | A  | |2  | B  | |3  | C  | |4  | D  | 

Answer 1

Here is how you could create such a table:

 SELECT LEVEL AS id, REGEXP_SUBSTR('A,B,C,D', '[^,]+', 1, LEVEL) AS data    FROM dual CONNECT BY REGEXP_SUBSTR('A,B,C,D', '[^,]+', 1, LEVEL) IS NOT NULL; 

With a little bit of tweaking (i.e., replacing the , in [^,] with a variable) you could write such a function to return a table.

Answer 2

There are multiple options. See Split single comma delimited string into rows in Oracle

You just need to add LEVEL in the select list as a column, to get the sequence number to each row returned. Or, ROWNUM would also suffice.

Using any of the below SQLs, you could include them into a FUNCTION.

INSTR in CONNECT BY clause:

 SQL> WITH DATA AS   2    ( SELECT 'word1, word2, word3, word4, word5, word6' str FROM dual   3    )   4  SELECT trim(regexp_substr(str, '[^,]+', 1, LEVEL)) str   5  FROM DATA   6  CONNECT BY instr(str, ',', 1, LEVEL - 1) > 0   7  /  STR ---------------------------------------- word1 word2 word3 word4 word5 word6  6 rows selected.  SQL> 

REGEXP_SUBSTR in CONNECT BY clause:

 SQL> WITH DATA AS   2    ( SELECT 'word1, word2, word3, word4, word5, word6' str FROM dual   3    )   4  SELECT trim(regexp_substr(str, '[^,]+', 1, LEVEL)) str   5  FROM DATA   6  CONNECT BY regexp_substr(str , '[^,]+', 1, LEVEL) IS NOT NULL   7  /  STR ---------------------------------------- word1 word2 word3 word4 word5 word6  6 rows selected.  SQL> 

REGEXP_COUNT in CONNECT BY clause:

 SQL> WITH DATA AS   2        ( SELECT 'word1, word2, word3, word4, word5, word6' str FROM dual   3        )   4      SELECT trim(regexp_substr(str, '[^,]+', 1, LEVEL)) str   5      FROM DATA   6      CONNECT BY LEVEL  

Using XMLTABLE

 SQL> WITH DATA AS   2    ( SELECT 'word1, word2, word3, word4, word5, word6' str FROM dual   3    )   4  SELECT trim(COLUMN_VALUE) str   5    FROM DATA, xmltable(('"' || REPLACE(str, ',', '","') || '"'))   6  / STR ------------------------------------------------------------------------ word1 word2 word3 word4 word5 word6  6 rows selected.  SQL> 

Using MODEL clause:

 SQL> WITH t AS   2  (   3         SELECT 'word1, word2, word3, word4, word5, word6' str   4         FROM   dual ) ,   5  model_param AS   6  (   7         SELECT str AS orig_str ,   8                ','   9                       || str  10                       || ','                                 AS mod_str ,  11                1                                             AS start_pos ,  12                Length(str)                                   AS end_pos ,  13                (Length(str) - Length(Replace(str, ','))) + 1 AS element_count ,  14                0                                             AS element_no ,  15                ROWNUM                                        AS rn  16         FROM   t )  17  SELECT   trim(Substr(mod_str, start_pos, end_pos-start_pos)) str  18  FROM     (  19                  SELECT *  20                  FROM   model_param MODEL PARTITION BY (rn, orig_str, mod_str)  21                  DIMENSION BY (element_no)  22                  MEASURES (start_pos, end_pos, element_count)  23                  RULES ITERATE (2000)  24                  UNTIL (ITERATION_NUMBER+1 = element_count[0])  25                  ( start_pos[ITERATION_NUMBER+1] = instr(cv(mod_str), ',', 1, cv(element_no)) + 1,  26                  end_pos[iteration_number+1] = instr(cv(mod_str), ',', 1, cv(element_no) + 1) ) )  27  WHERE    element_no != 0  28  ORDER BY mod_str ,  29           element_no  30  /  STR ------------------------------------------ word1 word2 word3 word4 word5 word6  6 rows selected.  SQL> 

You could also use DBMS_UTILITY package provided by Oracle. It provides various utility subprograms. One such useful utility is COMMA_TO_TABLE procedure, which converts a comma-delimited list of names into a PL/SQL table of names.

Read DBMS_UTILITY.COMMA_TO_TABLE