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