SQL Snippets ™: Tutorials for Oracle Developers

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
 

If we have a known, manageable maximum number of values in our string we can combine any one of the parsing techniques described in the SQL Techniques Tutorials: String to Columns tutorial section with a UNION ALL query to convert the string into a set of rows.

First, here is 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, here is a solution that uses parsing logic from String to Columns: Separated Values - 10g+.

set null "(null)"
set pagesize 45

variable d varchar2(1)

execute :d := ','

break on key skip 1 duplicates

select
  key,
  1 as position,
  rtrim( regexp_substr( c || :d, '.*?' || :d, 1, 1 ), :d ) as val
from   t
UNION ALL
select
  key,
  2 as position,
  rtrim( regexp_substr( c || :d, '.*?' || :d, 1, 2 ), :d ) as val
from   t
where  regexp_substr( c || :d, '.*?' || :d, 1, 2 ) is not null
UNION ALL
select
  key,
  3 as position,
  rtrim( regexp_substr( c || :d, '.*?' || :d, 1, 3 ), :d ) as val
from   t
where  regexp_substr( c || :d, '.*?' || :d, 1, 3 ) is not null
order by
  key, position, val ;
 

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)