splitting two columns containing comma separated values in oracle

Question

I have two columns in a table with comma separated values, how do it split it into rows?

there are lots of options out there, [for example](https://stackoverflow.com/questions/14328621/splitting-string-into-multiple-rows-in-oracle) — micklesh, May 12 '20 at 12:29
@micklesh Partly yes, but the issue is i have two columns with comma separated value in a single table , how can i split them? — Vinod N M, May 12 '20 at 12:33
a simplest (but INCORRECT) option - combine the 2 columns into a single value and consider that concatenated field. or treat the fields separately and combine the results afterwards — micklesh, May 12 '20 at 12:35
Does [this](https://stackoverflow.com/questions/13189575/listunagg-function) help? "listunagg function" — Ralf S, May 12 '20 at 12:45
The correct solution is to fix the data model. Don't store delimited data in a column — , May 12 '20 at 15:19

score 1 · Answer 1 · answered May 12 '20 at 15:17

Would this help?

SQL> with test (col1, col2) as
  2    (select 'Little,Foot,is,stupid', 'poor,bastard'         from dual union all
  3     select 'Green,mile,is,a'      , 'good,film,is,it,not?' from dual
  4    )
  5  select regexp_substr(col1 ||','|| col2, '[^,]+', 1, column_value) str
  6  from test cross join
  7       table(cast(multiset(select level from dual
  8                           connect by level <= regexp_count(col1 ||','|| col2, ',') + 1
  9                          ) as sys.odcinumberlist));

STR
--------------------------------------------------------------------------------
Little
Foot
is
stupid
poor
bastard
Green
mile
is
a
good
film
is
it
not?

15 rows selected.

SQL>

score 0 · Answer 2 · answered May 12 '20 at 12:47

Use a recursive sub-query factoring clause and simple string functions:

WITH splits ( id, c1, c2, idx, start_c1, end_c1, start_c2, end_c2 ) AS (
  SELECT id,
         c1,
         c2,
         1,
         1,
         INSTR( c1, ',', 1 ),
         1,
         INSTR( c2, ',', 1 )
  FROM   test_data
UNION ALL
  SELECT id,
         c1,
         c2,
         idx + 1,
         CASE end_c1 WHEN 0 THEN NULL ELSE end_c1 + 1 END,
         CASE end_c1 WHEN 0 THEN NULL ELSE INSTR( c1, ',', end_c1 + 1 ) END,
         CASE end_c2 WHEN 0 THEN NULL ELSE end_c2 + 1 END,
         CASE end_c2 WHEN 0 THEN NULL ELSE INSTR( c2, ',', end_c2 + 1 ) END
  FROM   splits
  WHERE  end_c1 > 0
  OR     end_c2 > 0
)
SELECT id,
       idx,
       CASE end_c1
       WHEN 0
       THEN SUBSTR( c1, start_c1 )
       ELSE SUBSTR( c1, start_c1, end_c1 - start_c1 )
       END AS c1,
       CASE end_c2
       WHEN 0
       THEN SUBSTR( c2, start_c2 )
       ELSE SUBSTR( c2, start_c2, end_c2 - start_c2 )
       END AS c2
FROM   splits s
ORDER BY id, idx;

So for the test data:

CREATE TABLE test_data ( id, c1, c2 ) AS
SELECT 1, 'a,b,c,d', 'e,f,g' FROM DUAL UNION ALL
SELECT 2, 'h', 'i' FROM DUAL UNION ALL
SELECT 3, NULL, 'j,k,l,m,n' FROM DUAL;

This outputs:

ID | IDX | C1   | C2  
-: | --: | :--- | :---
 1 |   1 | a    | e   
 1 |   2 | b    | f   
 1 |   3 | c    | g   
 1 |   4 | d    | null
 2 |   1 | h    | i   
 3 |   1 | null | j   
 3 |   2 | null | k   
 3 |   3 | null | l   
 3 |   4 | null | m   
 3 |   5 | null | n

db<>fiddle here

@VinodNM What you are asking is not "simple" so you are not going to find an easy one-line answer. This answer is pretty "simple" .... it just iterates over the strings finding commas in the recursive sub-query factoring clause and then in the bottom section finds the substrings between those commas. There are plenty of "other" ways including regular expressions, using self-joins to table collection expressions or custom functions but this is possibly one of the more "efficient" ways as it does not involve context switches to PL/SQL that a custom function would nor any joins. — MT0, May 12 '20 at 13:35

splitting two columns containing comma separated values in oracle

2 Answers2