String to Rows

Integer Series Generator Method

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
 

When strings contain a large number of values we need a more flexible and efficient technique than the method presented in UNION ALL Method. One strategy involves using a parsing technique like one of those described in SQL Techniques Tutorials: String to Columns with an integer series generator like the ones in SQL Techniques Tutorials: Integer Series Generators. While many different combinations of the two types of techniques are possible, we will only examine a couple of these combinations here.

Before we see the first solution 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 one possible solution.

set null "(null)"
set pagesize 45

break on key skip 1 duplicates

variable d varchar2(1)

execute :d := ','

select
  ta.key ,
  i.column_value as position ,
  substr
  ( ta.c2,
    instr( ta.c2, :d, 1, i.column_value  ) + 1,
    instr( ta.c2, :d, 1, i.column_value + 1 )
      - ( instr( ta.c2, :d, 1, i.column_value ) + 1 )
  )
  as val
from
  ( select
      key ,
      :d || c || :d as c2 ,
      length( c || :d ) - nvl( length( replace( c, :d, null ) ), 0 ) as value_count
    from t
  ) ta
      inner join table( integer_varray_type( 1,2,3,4,5 ) ) i
      on ( i.column_value <= ta.value_count )
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 query works consider its individual components.

Component 1

First we look at values derived from T.

clear breaks

select
  key ,
  c ,
  LENGTH( C || :D ) - NVL( LENGTH( REPLACE( C, :D, NULL ) ), 0 ) AS VALUE_COUNT
from  t
order by 1
;
 
KEY C          VALUE_COUNT
--- ---------- -----------
r01 v1                   1
r02 v1,                  2
r03 v1,,                 3
r04 ,v2,                 3
r05 ,,v3                 3
r06 v1,v2                2
r07 v1,v2,               3
r08 v1,v2,v3             3
r09 (null)               1
r10 ,                    2
r11 ,,                   3
 

In this query VALUE_COUNT is simply the total number of delimited values in the string, determined by deriving the total number of delimiter characters in the string plus one. If your delimiter is more than one character long you will need to adjust this logic to return a correct count.

Component 2

The next component is a simple integer series generator.

clear breaks

select * from table( integer_varray_type( 1,2,3,4,5 ) ) ;
 
COLUMN_VALUE
------------
           1
           2
           3
           4
           5
 

It contains a number of rows equal to the maximum number we expect to find in VALUE_COUNT. In our example we expect no more than 5 delimited values per string.

This technique is described in more detail at Integer Series Generators: Type Constructor Expression Method. It requires a custom collection type (created in the Setup code for this section) defined like this.

desc integer_varray_type
 integer_varray_type VARRAY(10) OF NUMBER(38)

 

If we did not have access to such a type or could not create our own there are other integer series generation techniques to choose from. See the feature graph at the end of SQL Techniques Tutorials: Integer Series Generators for a list of alternatives.

Components 1 + 2

Next we combine components 1 and 2 to form a single query that returns one row for each delimited value in each STRING.

break on key skip 1 duplicates

column c2 format a12

select
  ta.key, ta.c2, ta.value_count, i.column_value
from
  ( select
      key ,
      :d || c || :d as c2 ,
      LENGTH( C || :D ) - NVL( LENGTH( REPLACE( C, :D, NULL ) ), 0 ) AS VALUE_COUNT
    from t
  ) ta
    inner join TABLE( INTEGER_VARRAY_TYPE( 1,2,3,4,5 ) ) I
      on ( i.column_value <= ta.value_count )
order by
  1, 2, 3, 4
;
 
KEY C2           VALUE_COUNT COLUMN_VALUE
--- ------------ ----------- ------------
r01 ,v1,                   1            1

r02 ,v1,,                  2            1
r02 ,v1,,                  2            2

r03 ,v1,,,                 3            1
r03 ,v1,,,                 3            2
r03 ,v1,,,                 3            3

r04 ,,v2,,                 3            1
r04 ,,v2,,                 3            2
r04 ,,v2,,                 3            3

r05 ,,,v3,                 3            1
r05 ,,,v3,                 3            2
r05 ,,,v3,                 3            3

r06 ,v1,v2,                2            1
r06 ,v1,v2,                2            2

r07 ,v1,v2,,               3            1
r07 ,v1,v2,,               3            2
r07 ,v1,v2,,               3            3

r08 ,v1,v2,v3,             3            1
r08 ,v1,v2,v3,             3            2
r08 ,v1,v2,v3,             3            3

r09 ,,                     1            1

r10 ,,,                    2            1
r10 ,,,                    2            2

r11 ,,,,                   3            1
r11 ,,,,                   3            2
r11 ,,,,                   3            3
 
Component 3

To extract individual values from each string we use the parsing logic described in the String to Columns: Separated Values tutorial. Here is an example of how the logic extracts the first value from a delimited string.

column first_val format a9

select
  c2,
  instr( c2, :d, 1, 1 ) position_of_first_delim ,
  instr( c2, :d, 1, 2 ) position_of_second_delim ,
  substr
  ( c2,
    instr( c2, :d, 1, 1 ) + 1,        -- == position of 1st character in value
    instr( c2, :d, 1, 2 )
      - ( instr( c2, :d, 1, 1 ) + 1 ) -- == length of 1st value
  )
  as first_val
from
  ( select :d || c || :d as c2
    from   t
    where  key = 'r08'
  )
;
 
C2           POSITION_OF_FIRST_DELIM POSITION_OF_SECOND_DELIM FIRST_VAL
------------ ----------------------- ------------------------ ---------
,v1,v2,v3,                         1                        4 v1
 
Final Solution

Adding the parsing logic from Component 3 to the query from "Components 1 + 2" yields the final solution.

select
  ta.key ,
  i.column_value as position ,
  substr
  ( ta.c2,
    instr( ta.c2, :d, 1, i.column_value  ) + 1,
    instr( ta.c2, :d, 1, i.column_value + 1 )
      - ( instr( ta.c2, :d, 1, i.column_value ) + 1 )
  )
  as val
from
  ( select
      key ,
      :d || c || :d as c2 ,
      length( c || :d ) - nvl( length( replace( c, :d, null ) ), 0 ) as value_count
    from t
  ) ta
    inner join table( integer_varray_type( 1,2,3,4,5 ) ) i
      on ( i.column_value <= ta.value_count )
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)
 

For applications running on Oracle 10g the functions REGEXP_SUBSTR and REGEXP_REPLACE can help simplify the parsing and value counting logic used above. We'll see how in the next topic.

Acknowledgements

The VALUE_COUNT logic was adapted from The Tom Kyte Blog: Varying in lists....




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-12430.html]SQL Snippets: String to Rows - Integer Series Generator Method[/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-12430.html">SQL Snippets: String to Rows - Integer Series Generator Method</a>

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

  • Link Text : SQL Snippets: String to Rows - Integer Series Generator Method
  • URL (href): http://www.sqlsnippets.com/en/topic-12430.html