String to Columns

Quoted Values - 10g+

This tutorial shows how to extract quote delimited values from strings like these

DATA_SET                  STRING
------------------------- --------------------
Double Quotes             "a", "b,b", "c c c"

Single Quotes             'a', 'b,b', 'c c c'

Single Quotes No Commas   'a' 'b,b' 'c c c'
 

and return the values in separate columns like this.

DATA_SET                  VAL_1  VAL_2  VAL_3
------------------------- ------ ------ ------
Double Quotes             a      b,b    c c c

Single Quotes             a      b,b    c c c

Single Quotes No Commas   a      b,b    c c c
 

The solution in this tutorial uses features introduced in Oracle 10g. For Oracle databases running versions earlier than 10g see the Quoted Values tutorial.

The solution for Oracle 10g+ uses the REGEXP_REPLACE function to locate and extract each quoted value from each string. (See SQL Features Tutorials: Regular Expressions if you are not familiar with regular expression support in Oracle.)

column string format a20

break on data_set skip 1

-- "d" stands for Delimiter
variable d        varchar2(1)
variable data_set varchar2(25)

execute :d := '''' ; :data_set := 'Single Quotes%'

select
  data_set ,
  num ,
  string ,
  trim( :d from regexp_substr( string, :d || '.*?' || :d, 1, 1 ) ) as val_1 ,
  trim( :d from regexp_substr( string, :d || '.*?' || :d, 1, 2 ) ) as val_2 ,
  trim( :d from regexp_substr( string, :d || '.*?' || :d, 1, 3 ) ) as val_3
from
  t1
where
  data_set like :data_set
order by
  data_set, num
;
 
DATA_SET                   NUM STRING               VAL_1  VAL_2  VAL_3
------------------------- ---- -------------------- ------ ------ ------
Single Quotes                1 'a'                  a
                             2 'a', ''              a
                             3 'a', '', ''          a
                             4 '', 'b,b', ''               b,b
                             5 '', '', 'c c c'                    c c c
                             6 'a', 'b,b'           a      b,b
                             7 'a', 'b,b', ''       a      b,b
                             8 'a', 'b,b', 'c c c'  a      b,b    c c c
                             9 ''
                            10 '', ''
                            11 '', '', ''
                            12

Single Quotes No Commas      1 'a'                  a
                             2 'a' ''               a
                             3 'a' '' ''            a
                             4 '' 'b,b' ''                 b,b
                             5 '' '' 'c c c'                      c c c
                             6 'a' 'b,b'            a      b,b
                             7 'a' 'b,b' ''         a      b,b
                             8 'a' 'b,b' 'c c c'    a      b,b    c c c
                             9 ''
                            10 '' ''
                            11 '' '' ''
                            12
 
execute :d := '"' ; :data_set := 'Double Quotes%'

/
 
DATA_SET                   NUM STRING               VAL_1  VAL_2  VAL_3
------------------------- ---- -------------------- ------ ------ ------
Double Quotes                1 "a"                  a
                             2 "a", ""              a
                             3 "a", "", ""          a
                             4 "", "b,b", ""               b,b
                             5 "", "", "c c c"                    c c c
                             6 "a", "b,b"           a      b,b
                             7 "a", "b,b", ""       a      b,b
                             8 "a", "b,b", "c c c"  a      b,b    c c c
                             9 ""
                            10 "", ""
                            11 "", "", ""
                            12
 

To include the quote delimiters in the extracted value simply exclude the TRIM() function from each expression.

execute :d := '''' ; :data_set := 'Single Quotes'

select
  data_set ,
  num ,
  string ,
  regexp_substr( string, :d || '.*?' || :d, 1, 1 ) as val_1 ,
  regexp_substr( string, :d || '.*?' || :d, 1, 2 ) as val_2 ,
  regexp_substr( string, :d || '.*?' || :d, 1, 3 ) as val_3
from
  t1
where
  data_set like :data_set
order by
  data_set, num
;
 
DATA_SET                   NUM STRING               VAL_1  VAL_2  VAL_3
------------------------- ---- -------------------- ------ ------ ------
Single Quotes                1 'a'                  'a'
                             2 'a', ''              'a'    ''
                             3 'a', '', ''          'a'    ''     ''
                             4 '', 'b,b', ''        ''     'b,b'  ''
                             5 '', '', 'c c c'      ''     ''     'c c c
                                                                  '

                             6 'a', 'b,b'           'a'    'b,b'
                             7 'a', 'b,b', ''       'a'    'b,b'  ''
                             8 'a', 'b,b', 'c c c'  'a'    'b,b'  'c c c
                                                                  '

                             9 ''                   ''
                            10 '', ''               ''     ''
                            11 '', '', ''           ''     ''     ''
                            12
 

If the quoted values contain no commas or spaces and we wish to ignore empty strings we can use the following technique which is slightly more compact than our first solution.

execute :d := '''' ; :data_set := 'Single Quotes'

select
  data_set ,
  num ,
  string ,
  regexp_substr( string, '[^' || :d || ', ]+', 1, 1 ) as val_1 ,
  regexp_substr( string, '[^' || :d || ', ]+', 1, 2 ) as val_2 ,
  regexp_substr( string, '[^' || :d || ', ]+', 1, 3 ) as val_3
from
  ( select
      data_set,
      num,
      replace( replace( string, 'c c c', 'ccc' ), 'b,b', 'bb' ) as string
    from t1
  ) t1a
where
  data_set like :data_set
order by
  data_set, num
;
 
DATA_SET                   NUM STRING               VAL_1  VAL_2  VAL_3
------------------------- ---- -------------------- ------ ------ ------
Single Quotes                1 'a'                  a
                             2 'a', ''              a
                             3 'a', '', ''          a
                             4 '', 'bb', ''         bb
                             5 '', '', 'ccc'        ccc
                             6 'a', 'bb'            a      bb
                             7 'a', 'bb', ''        a      bb
                             8 'a', 'bb', 'ccc'     a      bb     ccc
                             9 ''
                            10 '', ''
                            11 '', '', ''
                            12
 

Note the results for strings 4 and 5 differ from those of prior solutions. This effect may or may not be useful depending on your requirements.

Acknowledgements:

See comments by OTN users cd and ebrian at Re: use of Regular Expression...




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-11581.html]SQL Snippets: String to Columns - Quoted Values - 10g+[/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-11581.html">SQL Snippets: String to Columns - Quoted Values - 10g+</a>

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

  • Link Text : SQL Snippets: String to Columns - Quoted Values - 10g+
  • URL (href): http://www.sqlsnippets.com/en/topic-11581.html

Revision Notes

Date Category Note
2007-03-30 Revision This tutorial was moved from parent topic-11578.html to parent topic-11998.html.
2007-03-30 Revision The original solution has been improved upon by using TRIM instead of REPLACE in certain expressions.