>

Convert a CSV String into Rows using OracleSQL

Posted on December 5, 2011 By

The is the Oracle version of an SQL Solution which can transform a string of CSV values into rows, with one row per value.  The SQL snippet uses a delimited string, and will convert this string to rows using the delimiter.  Assume you have a Comma Seperated Value (CSV) string such as: a,b,c,d and need to convert it to rows.  The SQL snippet below will do this for you.

1
2
3
4
5
6
7
select substr(csv,
instr(csv,',',1,lev) + 1,
instr(csv,',',1,lev+1 )-instr(csv,',',1,lev)-1)
from
(select ','||'a,b,c,d'||',' csv from dual),
(select level lev from dual connect by level <= 100)
where lev <= length(csv)-length(replace(csv,','))-1;

How does it work?
The select statement on line 5 will append leading and trailing commas and will return one row: ,a,b,c,d, from dual. 

The select statement on line 6 selects the numbers 1 through 100 – each on a different row.  We have a seperate post explaining how to select sequential numbers from dual.

The result of joining this two statements is:

csv lev
,a,b,c,d, 1
,a,b,c,d, 2
,a,b,c,d, 3
,a,b,c,d, 4
,a,b,c,d, 5
,a,b,c,d, 6
… …
,a,b,c,d, 99
,a,b,c,d, 100

The where clause on line 7 restricts this result set to contain the same number of rows as the number of commas in the original string minus one (after line 5 appends the commas); this is the number of items in the CSV list.  we have a seperate post that describes how this  count of characters work.  This gives the result set below.

csv lev
,a,b,c,d, 1
,a,b,c,d, 2
,a,b,c,d, 3
,a,b,c,d, 4

Notice that we now have one row for each item, and the lev column is the position of a sequential item in the list.  Now, we just need to pick the first item in the list on row 1, the second item in the list on row 3, and so on.  We do this with the substr on line 1-3, using the value in the lev colum.

In this example I have arbitrarily picked the number 100 on line 6.  This number must be higher than the number of items in your list.   You can hard-code an arbitrarily high number or use the length of the CSV string.  You could even use the count of comma (plus one) from the original CSV string.

SQL     , ,