plsql - SQL - Splitting a string into a new row -

i have table with 2 columns shown below:

a001    a1;a2;a3 b002    b1 c003    c1;c2 d004    d1;d2;d3;d4 e005    e1 

the table has 2 columns, second column has contains either single value or multiple values string separated semicolon.

what each of rows value string multiple values, want split string , use each value insert new row (duplicating value first column new row).

with above sample data, output this:

a001 a1 a001 a2 a001 a3 b002 b1 c003 c1 c003 c2 d004 d1 d004 d2 d004 d3 d004 d4 e005 e1 

with src (         select 'a001' col1, 'a1;a2;a3' col2 dual union         select 'b002',      'b1'            dual union         select 'c003',      'c1;c2'         dual union         select 'd004',      'd1;d2;d3;d4'   dual union         select 'e005',      'e1'            dual ) , explode (         select  col1         ,       regexp_substr(col2, '\w+', 1, 1) col2_1         ,       regexp_substr(col2, '\w+', 1, 2) col2_2         ,       regexp_substr(col2, '\w+', 1, 3) col2_3         ,       regexp_substr(col2, '\w+', 1, 4) col2_4         --      if there more add more...            src ) select col1, col2_1 explode col2_1 not null union select col1, col2_2 explode col2_2 not null union select col1, col2_3 explode col2_3 not null union select col1, col2_4 explode col2_4 not null  order col1 ; 

the result:

/* a001    a1 a001    a2 a001    a3 b002    b1 c003    c2 c003    c1 d004    d1 d004    d4 d004    d2 d004    d3 e005    e1 */