Oracle: Convert CSV String into Rows

This nifty piece of code takes a comma separated string and converts it into rows.

I’m using sqlplus and a variable to simulate the data.

The data:

VAR csv VARCHAR2(100)
EXEC :csv := 'abc,de,fg,hij,klmn,o,pq,rst,uvw,xyz';

The query:

WITH data AS
( SELECT SUBSTR(csv, INSTR(csv,',',1,LEVEL)+1,
INSTR(csv,',',1,LEVEL+1) - INSTR(csv,',',1,LEVEL)-1 ) token
FROM ( SELECT ','||:csv||',' csv FROM SYS.DUAL )
CONNECT BY LEVEL < LENGTH(:csv)-LENGTH(REPLACE(:csv,',',''))+2 )
SELECT token
FROM data;

The result:

TOKEN
-----------------
abc
de
fg
hij
klmn
o
pq
rst
uvw
xyz

10 rows selected.

Just replace the inline query SELECT ','||:csv||',' csv FROM SYS.DUAL to whatever will return your csv string, (but keep the outer commas).

Split.