String to Rows

Hierarchical Methods

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).




Linking to SQL Snippets ™

To link to this page in Oracle Technology Network Forums or OraFAQ Forums cut and paste this code.

  • [url=http://www.sqlsnippets.com/en/topic-12511.html]SQL Snippets: String to Rows - Hierarchical Methods[/url]

To link to this page in HTML documents or Blogger comments cut and paste this code.

  • <a href="http://www.sqlsnippets.com/en/topic-12511.html">SQL Snippets: String to Rows - Hierarchical Methods</a>

To link to this page in other web sites use the following values.

  • Link Text : SQL Snippets: String to Rows - Hierarchical Methods
  • URL (href): http://www.sqlsnippets.com/en/topic-12511.html