Tags

, , , , , ,

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!