Split function in oracle to comma separated values with automatic sequence

Here is how you could create such a table:

 SELECT LEVEL AS id, REGEXP_SUBSTR('A,B,C,D', '[^,]+', 1, LEVEL) AS data   FROM dualCONNECT 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.

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----------------------------------------word1word2word3word4word5word66 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----------------------------------------word1word2word3word4word5word66 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------------------------------------------------------------------------word1word2word3word4word5word66 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------------------------------------------word1word2word3word4word5word66 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

Oracle Setup:

CREATE OR REPLACE FUNCTION split_String(  i_str    IN  VARCHAR2,  i_delim  IN  VARCHAR2 DEFAULT ',') RETURN SYS.ODCIVARCHAR2LIST DETERMINISTICAS  p_result       SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST();  p_start        NUMBER(5) := 1;  p_end          NUMBER(5);  c_len CONSTANT NUMBER(5) := LENGTH( i_str );  c_ld  CONSTANT NUMBER(5) := LENGTH( i_delim );BEGIN  IF c_len > 0 THEN    p_end := INSTR( i_str, i_delim, p_start );    WHILE p_end > 0 LOOP      p_result.EXTEND;      p_result( p_result.COUNT ) := SUBSTR( i_str, p_start, p_end - p_start );      p_start := p_end + c_ld;      p_end := INSTR( i_str, i_delim, p_start );    END LOOP;    IF p_start <= c_len + 1 THEN      p_result.EXTEND;      p_result( p_result.COUNT ) := SUBSTR( i_str, p_start, c_len - p_start + 1 );    END IF;  END IF;  RETURN p_result;END;/

Query

SELECT ROWNUM AS ID,       COLUMN_VALUE AS DataFROM   TABLE( split_String( 'A,B,C,D' ) );

Output:

ID DATA-- ---- 1 A 2 B 3 C 4 D