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
While the solution below works in any Oracle version, the syntax is moderately complex. On Oracle databases running 10g or greater a more compact solution is available in the Quoted Values - 10g+ tutorial.
The solution for any Oracle version uses the SUBSTR and INSTR functions to locate and extract each quoted value from each string.
In the following example we see how to extract values surrounded by single quotes.
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 ,
substr
( string ,
instr( string, :d, 1, 1 ) + 1 ,
instr( string, :d, 1, 2 ) - instr( string, :d, 1, 1 ) - 1
)
as val_1 ,
substr
( string ,
instr( string, :d, 1, 3 ) + 1 ,
instr( string, :d, 1, 4 ) - instr( string, :d, 1, 3 ) - 1
)
as val_2 ,
substr
( string ,
instr( string, :d, 1, 5 ) + 1 ,
instr( string, :d, 1, 6 ) - instr( string, :d, 1, 5 ) - 1
)
as val_3
from t1
where data_set = :data_set
order by 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
The technique works equally well with values surrounded by double quotes.
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
No separator is required between quoted values with this technique. In the next example we see that it works even when there are no commas separating the values.
execute :d := '''' ; :data_set := 'Single Quotes No Commas' /
DATA_SET NUM STRING VAL_1 VAL_2 VAL_3
------------------------- ---- -------------------- ------ ------ ------
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