This tutorial shows how to extract comma separated values (CSV), pipe delimited values,
or tab delimited values from strings like these
(the
·
symbol indicates a single TAB character, i.e. ASCII code 9)
DATA_SET STRING ------------------------- --------------- CSV a,bb,c c c Pipe Delimited a|b,b|c c c Tab Delimited a·b,b·c c c
and return the values in separate columns like this.
DATA_SET VAL_1 VAL_2 VAL_3 ------------------------- ------ ------ ------ CSV a bb c c c Pipe Delimited a b,b c c c Tab Delimited 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 Separated Values - 10g+ tutorial.
The solution for any Oracle version uses the SUBSTR and INSTR functions to locate and extract each delimited value from each string.
In the following example we see how to extract comma separated values.
column string format a15
break on data_set skip 1
-- "d" stands for Delimiter
variable d varchar2(1)
variable data_set varchar2(25)
execute :d := ',' ; :data_set := 'CSV'
select
data_set ,
num ,
string ,
substr
( string ,
1 ,
instr( string||:d, :d, 1, 1 ) - 1
)
as val_1 ,
substr
( string ,
instr( string||:d, :d, 1, 1 ) + 1 ,
instr( string||:d, :d, 1, 2 ) - instr( string||:d, :d, 1, 1 ) - 1
)
as val_2 ,
substr
( string ,
instr( string||:d, :d, 1, 2 ) + 1 ,
instr( string||:d, :d, 1, 3 ) - instr( string||:d, :d, 1, 2 ) - 1
)
as val_3
from t1
where data_set = :data_set
order by num
;
DATA_SET NUM STRING VAL_1 VAL_2 VAL_3
------------------------- ---- --------------- ------ ------ ------
CSV 1 a a
2 a, a
3 a,, a
4 ,bb, bb
5 ,,c c c c c c
6 a,bb a bb
7 a,bb, a bb
8 a,bb,c c c a bb c c c
9
10 ,
11 ,,
The technique works equally well with pipe delimited values and tab delimited values.
execute :d := '|' ; :data_set := 'Pipe Delimited' /
DATA_SET NUM STRING VAL_1 VAL_2 VAL_3
------------------------- ---- --------------- ------ ------ ------
Pipe Delimited 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 := chr(9) ; :data_set := 'Tab Delimited' /
DATA_SET NUM STRING VAL_1 VAL_2 VAL_3
------------------------- ---- --------------- ------ ------ ------
Tab Delimited 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