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.
POSITION VAL
---------- ----------
1 v1
2 v2
3 v3
The following solution uses the SQL MODEL clause of the SELECT command. See the SQL Features Tutorials: MODEL Clause tutorial if you are unfamiliar with MODEL.
Test Data
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
Solution
set null "(null)"
set pagesize 45
break on key skip 1 duplicates
variable d varchar2(1)
execute :d := ','
select key, position, val
from t
model
return updated rows
partition by ( key )
dimension by ( 0 as position )
measures ( c || :d as val )
rules upsert
( val
[ for position
from 1
to length( regexp_replace( val[0], '[^'||:d||']', null ) )
increment 1
] = rtrim( regexp_substr( val[0], '.*?' || :d, 1, cv(position) ), :d )
)
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 It Works
To understand how the solution works consider the following queries which represent intermediate steps in the query processing.
select key, position, val from t where key = 'r08' model partition by ( key ) dimension by ( 0 as position ) measures ( c || :d as val ) rules ( ) order by key, position ;
KEY POSITION VAL --- ---------- ---------- r08 0 v1,v2,v3,
select key, position, val from t where key = 'r08' model partition by ( key ) dimension by ( 0 as position ) measures ( c || :d as val ) rules upsert ( val[ 1 ] = rtrim( regexp_substr( val[0], '.*?' || :d, 1, cv(position) ), :d ) ) order by key, position ;
KEY POSITION VAL --- ---------- ---------- r08 0 v1,v2,v3, r08 1 v1
select key, position, val
from t
where key = 'r08'
model
partition by ( key )
dimension by ( 0 as position )
measures ( c || :d as val )
rules upsert
( val[ 1 ] = rtrim( regexp_substr( val[0], '.*?' || :d, 1, cv(position) ), :d ) ,
val[ 2 ] = rtrim( regexp_substr( val[0], '.*?' || :d, 1, cv(position) ), :d )
)
order by key, position ;
KEY POSITION VAL --- ---------- ---------- r08 0 v1,v2,v3, r08 1 v1 r08 2 v2
select key, position, val
from t
where key = 'r08'
model
partition by ( key )
dimension by ( 0 as position )
measures ( c || :d as val )
rules upsert
( val
[ for position
from 1
to length( regexp_replace( val[0], '[^'||:d||']', null ) )
increment 1
] = rtrim( regexp_substr( val[0], '.*?' || :d, 1, cv(position) ), :d )
)
order by key, position ;
KEY POSITION VAL --- ---------- ---------- r08 0 v1,v2,v3, r08 1 v1 r08 2 v2 r08 3 v3
select key, position, val
from t
where key = 'r08'
model
RETURN UPDATED ROWS
partition by ( key )
dimension by ( 0 as position )
measures ( c || :d as val )
rules upsert
( val
[ for position
from 1
to length( regexp_replace( val[0], '[^'||:d||']', null ) )
increment 1
] = rtrim( regexp_substr( val[0], '.*?' || :d, 1, cv(position) ), :d )
)
order by key, position ;
KEY POSITION VAL --- ---------- ---------- r08 1 v1 r08 2 v2 r08 3 v3