Oracle split table column delimited values to to rows

Question

Need to help with following select col1, col2, split(col3) from test;

Table values:

Col1 col2 col3
xxx  yyy  a,b,c
iii  jjj  1,2,3

col3 contains comma-delimited values.

looking to achieve the following result. Splitting Col3 values for same col1 and col2 vlaue.

col1 col2 split
xxx  yyy  a
xxx  yyy  b
xxx  yyy  c
iii  jjj  1
iii  jjj  2
iii  jjj  3

Any regex or pl/sql function idea to help with this.

Answer 1

One option uses a standard recursive query:

with cte (col1, col2, pos, split, rest) as (
    select col1, col2, 1,
        substr(col3 || ',', 1, instr(col3 || ',', ',') - 1), 
        substr(col3 || ',', instr(col3 || ',', ',') + 1)
    from mytable 
    union all
    select col1, col2, pos + 1,
        substr(rest, 1, instr(rest, ',') - 1),
        substr(rest, instr(rest, ',') + 1)
    from cte 
    where instr(rest, ',') > 0
)
select col1, col2, split, pos from cte order by col1, col2, pos

Demo on DB Fiddlde:

COL1 | COL2 | SPLIT | POS
:--- | :--- | :---- | --:
iii  | jjj  | 1     |   1
iii  | jjj  | 2     |   2
iii  | jjj  | 3     |   3
xxx  | yyy  | a     |   1
xxx  | yyy  | b     |   2
xxx  | yyy  | c     |   3

Answer 2

One option would be using Regular Expressions in order to split by commas and count the comma-seperated portions for the column col3 within a Hierarchical Query :

 SELECT col1, col2, REGEXP_SUBSTR(col3,'[^,]',1,level) AS split
   FROM t
CONNECT BY level <= REGEXP_COUNT(col3,',') + 1
    AND PRIOR SYS_GUID() IS NOT NULL
    AND PRIOR col1 = col1;

COL1    COL2    SPLIT
iii     jjj     1
iii     jjj     2
iii     jjj     3
xxx     yyy     a
xxx     yyy     b
xxx     yyy     c

Demo