Tags
comma delimited values in table, comma separated column values, split column to rows, split comma delimited string, split comma delimited values in table, split comma separated column values into rows, split string
NOTE : This post is about splitting multiple comma delimited column values in a table. If you have to apply it on a single value then look at Split single comma delimited string into rows in Oracle
In my other post I summarized various methods to split a comma delimited string into rows using Oracle SQL. However, those SQLs would only take care of a single row, i.e. when you have a single value of comma delimited string. Usually, at our work place, we deal with tables. So, this post is to demonstrate “How to split comma delimited column values in a table into multiple rows in Oracle”
Basic setup:
SQL> DROP TABLE t PURGE;
Table dropped.
SQL> CREATE TABLE t
2 (id NUMBER generated always AS identity, text VARCHAR2(4000)
3 );
Table created.
SQL> INSERT INTO t (text) VALUES ('word1, word2, word3');
1 row created.
SQL> INSERT INTO t (text) VALUES ('word4, word5, word6');
1 row created.
SQL> INSERT INTO t (text) VALUES ('word7, word8, word9');
1 row created.
SQL> INSERT INTO t (text) VALUES ('word10, word11, word12, word13');
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM t;
ID TEXT
---------- ------------------------------
1 word1, word2, word3
2 word4, word5, word6
3 word7, word8, word9
4 word10, word11, word12, word13
SQL>
Now, let’s see the various methods to split the comma delimited values into rows from the table.
Using REGULAR EXPRESSIONS
Old Oracle Join Syntax for cross join :
INSTR in CONNECT BY clause
SQL> SELECT t.id,
2 trim(regexp_substr(t.text, '[^,]+', 1, lines.column_value)) text
3 FROM t,
4 TABLE (CAST (MULTISET
5 (SELECT LEVEL FROM dual
6 CONNECT BY instr(t.text, ',', 1, LEVEL - 1) > 0
7 ) AS sys.odciNumberList ) ) lines
8 ORDER BY id, lines.column_value
9 /
ID TEXT
---------- ------------------------------
1 word1
1 word2
1 word3
2 word4
2 word5
2 word6
3 word7
3 word8
3 word9
4 word10
4 word11
4 word12
4 word13
13 rows selected.
SQL>
REGEXP_SUBSTR in CONNECT BY clause
SQL> SELECT t.id,
2 trim(regexp_substr(t.text, '[^,]+', 1, lines.column_value)) text
3 FROM t,
4 TABLE (CAST (MULTISET
5 (SELECT LEVEL FROM dual
6 CONNECT BY regexp_substr(t.text , '[^,]+', 1, LEVEL) IS NOT NULL
7 ) AS sys.odciNumberList ) ) lines
8 ORDER BY id, lines.column_value
9 /
ID TEXT
---------- ------------------------------
1 word1
1 word2
1 word3
2 word4
2 word5
2 word6
3 word7
3 word8
3 word9
4 word10
4 word11
4 word12
4 word13
13 rows selected.
SQL>
REGEXP_COUNT in CONNECT BY clause
SQL> SELECT t.id,
2 trim(regexp_substr(t.text, '[^,]+', 1, lines.column_value)) text
3 FROM t,
4 TABLE (CAST (MULTISET
5 (SELECT LEVEL FROM dual
6 CONNECT BY LEVEL <= regexp_count(t.text, ',')+1
7 ) AS sys.odciNumberList ) ) lines
8 ORDER BY id, lines.column_value
9 /
ID TEXT
---------- ------------------------------
1 word1
1 word2
1 word3
2 word4
2 word5
2 word6
3 word7
3 word8
3 word9
4 word10
4 word11
4 word12
4 word13
13 rows selected.
SQL>
ANSI JOIN syntax :
REPLACE in CONNECT BY clause
SQL> SELECT id, text FROM(
2 SELECT t.ID,
3 trim(regexp_substr(t.text, '[^,]+', 1, lines.COLUMN_VALUE)) text,
4 lines.column_value cv
5 FROM t
6 CROSS JOIN
7 (SELECT *
8 FROM TABLE (CAST (MULTISET
9 (SELECT LEVEL
10 FROM dual
11 CONNECT BY LEVEL <=
12 (SELECT COUNT(REPLACE(text, ',')) FROM t
13 )
14 ) AS sys.odciNumberList ) )
15 ) lines
16 )
17 WHERE text IS NOT NULL
18 ORDER BY id, cv
19 /
ID TEXT
---------- ------------------------------
1 word1
1 word2
1 word3
2 word4
2 word5
2 word6
3 word7
3 word8
3 word9
4 word10
4 word11
4 word12
4 word13
13 rows selected.
SQL>
Using XMLTABLE
SQL> SELECT id,
2 trim(COLUMN_VALUE) text
3 FROM t,
4 xmltable(('"'
5 || REPLACE(text, ',', '","')
6 || '"'))
7 /
ID TEXT
---------- ------------------------------
1 word1
1 word2
1 word3
2 word4
2 word5
2 word6
3 word7
3 word8
3 word9
4 word10
4 word11
4 word12
4 word13
13 rows selected.
SQL>
Using MODEL clause
SQL> WITH
2 model_param AS
3 (
4 SELECT ID,
5 text AS orig_str ,
6 ','
7 || text
8 || ',' AS mod_str ,
9 1 AS start_pos ,
10 Length(text) AS end_pos ,
11 (LENGTH(text) -
12 LENGTH(REPLACE(text, ','))) + 1 AS element_count ,
13 0 AS element_no ,
14 ROWNUM AS rn
15 FROM t )
16 SELECT id,
17 trim(Substr(mod_str, start_pos, end_pos-start_pos)) text
18 FROM (
19 SELECT *
20 FROM model_param
21 MODEL PARTITION BY (id, rn, orig_str, mod_str)
22 DIMENSION BY (element_no)
23 MEASURES (start_pos, end_pos, element_count)
24 RULES ITERATE (2000)
25 UNTIL (ITERATION_NUMBER+1 = element_count[0])
26 ( start_pos[ITERATION_NUMBER+1] =
27 instr(cv(mod_str), ',', 1, cv(element_no)) + 1,
28 end_pos[ITERATION_NUMBER+1] =
29 instr(cv(mod_str), ',', 1, cv(element_no) + 1) )
30 )
31 WHERE element_no != 0
32 ORDER BY ID,
33 mod_str ,
34 element_no
35 /
ID TEXT
---------- ------------------------------
1 word1
1 word2
1 word3
2 word4
2 word5
2 word6
3 word7
3 word8
3 word9
4 word10
4 word11
4 word12
4 word13
13 rows selected.
SQL>
Hope it helps!