This tutorial shows how to extract delimited values from a string like this
C ---------- v1,v2,v3
and return them one value per row, like this.
KEY POSITION VAL --- ---------- ---------- r08 1 v1 r08 2 v2 r08 3 v3
The technique described here uses REGEXP_SUBSTR for parsing and hierarchical features to create and limit multiple copies of each input row. REGEXP functions are only available in Oracle 10g or above.
Before we see the solutions though, let's have a look at the test data we will use.
select * from t order by key ;
KEY C NOTE --- ---------- ------------------------- r01 v1 One value r02 v1, Two values, one empty r03 v1,, Three values, two empty r04 ,v2, Three values, two empty r05 ,,v3 Three values, two empty r06 v1,v2 Two values r07 v1,v2, Three values, one empty r08 v1,v2,v3 Three values, none empty r09 (null) One empty value r10 , Two values, two empty r11 ,, Three values, all empty
Now let's examine two possible solutions.
DBMS_RANDOM Solution
set null "(null)" set pagesize 45 break on key skip 1 duplicates variable d varchar2(1) execute :d := ',' select key, level as position , rtrim( regexp_substr( c || :d, '.*?' || :d, 1, level ), :d ) as val from t connect by key = prior key and prior dbms_random.value is not null and level <= length( regexp_replace( c || :d, '[^'||:d||']', null ) ) order by key, position ;
KEY POSITION VAL --- ---------- ---------- r01 1 v1 r02 1 v1 r02 2 (null) r03 1 v1 r03 2 (null) r03 3 (null) r04 1 (null) r04 2 v2 r04 3 (null) r05 1 (null) r05 2 (null) r05 3 v3 r06 1 v1 r06 2 v2 r07 1 v1 r07 2 v2 r07 3 (null) r08 1 v1 r08 2 v2 r08 3 v3 r09 1 (null) r10 1 (null) r10 2 (null) r11 1 (null) r11 2 (null) r11 3 (null)
CONNECT_BY_ROOT Solution
select key, level as position , rtrim( regexp_substr( c || :d, '.*?' || :d, 1, level ), :d ) as val from t connect by key = connect_by_root key and level <= length( regexp_replace( c || :d, '[^'||:d||']', null ) ) order by key, position ;
KEY POSITION VAL --- ---------- ---------- r01 1 v1 r02 1 v1 r02 2 (null) r03 1 v1 r03 2 (null) r03 3 (null) r04 1 (null) r04 2 v2 r04 3 (null) r05 1 (null) r05 2 (null) r05 3 v3 r06 1 v1 r06 2 v2 r07 1 v1 r07 2 v2 r07 3 (null) r08 1 v1 r08 2 v2 r08 3 v3 r09 1 (null) r10 1 (null) r10 2 (null) r11 1 (null) r11 2 (null) r11 3 (null)
How They Work
The REGEXP logic is explained in topic Integer Series Generator Method - 10g+. The hierarchical logic is explained in topic Multiple Integer Series: CONNECT BY LEVEL Method.
Gotchas
The CONNECT_BY_ROOT solution may not function without error in all cases or in Oracle releases beyond 10g. See the "Gotchas" section of topic Multiple Integer Series: CONNECT BY LEVEL Method for more details.
The CONNECT_BY_ROOT solution also performs significantly worse than the DBMS_RANDOM solution (see Performance Comparison).