Convert String to table with pipe seperated and comma seperated-CodePudding

Convert string to table using REGULAR EXPRESSION IN SQL i.e

A,B,C|D,E,F|I,J,K

and the output is

c1    c2   c3
A     B    C
D     E    F
G     H    I

CodePudding user response：

Here's one option (read comments within code):

SQL> with
  2  test (id, col) as
  3    -- sample data
  4    (select 1, 'A,B,C|D,E,F|I,J,K' from dual union all
  5     select 2, 'X|Y,Z|W'           from dual
  6    ),
  7  temp as
  8    -- split pipe-delimited values into rows
  9    (select id,
 10            regexp_substr(col, '[^|] ', 1, column_value) val
 11     from test cross join table(cast(multiset(select level from dual
 12                                              connect by level <= regexp_count(col, '\|')   1
 13                                             ) as sys.odcinumberlist))
 14    )
 15  -- finally, separate columns
 16  select id,
 17    regexp_substr(val, '\w ', 1, 1) c1,
 18    regexp_substr(val, '\w ', 1, 2) c2,
 19    regexp_substr(val, '\w ', 1, 3) c3
 20  from temp
 21  order by id;

        ID C1                C2                C3
---------- ----------------- ----------------- -----------------
         1 A                 B                 C
         1 D                 E                 F
         1 I                 J                 K
         2 X
         2 Y                 Z
         2 W

6 rows selected.

SQL>

It gets somewhat simpler if there's always only one pipe-delimited string:

SQL> with
  2  test (id, col) as
  3    -- sample data
  4    (select 1, 'A,B,C|D,E,F|I,J,K' from dual
  5    ),
  6  temp as
  7    -- split pipe-delimited values into rows
  8    (select id,
  9            regexp_substr(col, '[^|] ', 1, level) val
 10     from test
 11     connect by level <= regexp_count(col, '\|')   1
 12    )
 13  -- finally, separate columns
 14  select id,
 15    regexp_substr(val, '\w ', 1, 1) c1,
 16    regexp_substr(val, '\w ', 1, 2) c2,
 17    regexp_substr(val, '\w ', 1, 3) c3
 18  from temp
 19  order by id;

        ID C1                C2                C3
---------- ----------------- ----------------- -----------------
         1 A                 B                 C
         1 D                 E                 F
         1 I                 J                 K

SQL>

CodePudding user response：

Using string functions will be much more efficient than regular expressions:

WITH row_bounds ( value, s_pos, c1_pos, c2_pos, e_pos ) AS (
  SELECT value,
         1,
         INSTR(value, ',', 1, 1),
         INSTR(value, ',', 1, 2),
         INSTR(value, '|', 1)
  FROM   table_name
UNION ALL
  SELECT value,
         e_pos   1,
         INSTR(value, ',', e_pos   1, 1),
         INSTR(value, ',', e_pos   1, 2),
         INSTR(value, '|', e_pos   1)
  FROM   row_bounds
  WHERE  e_pos > 0
)
SEARCH DEPTH FIRST BY value SET v_order
SELECT SUBSTR(value, s_pos, c1_pos - s_pos) AS value1,
       SUBSTR(value, c1_pos   1, c2_pos - c1_pos - 1) AS value2,
       CASE e_pos
       WHEN 0
       THEN SUBSTR(value, c2_pos   1)
       ELSE SUBSTR(value, c2_pos   1, e_pos - c2_pos - 1)
       END AS value3
FROM   row_bounds;

Which, for the sample data:

CREATE TABLE table_name (value) AS
SELECT 'A,B,C|D,E,F|I,J,K' FROM DUAL;

Outputs:

VALUE1 VALUE2 VALUE3

A B C

D E F

I J K

VALUE1	VALUE2	VALUE3
A	B	C
D	E	F
I	J	K

db<>fiddle here