The Oracle (tm) Users' Co-Operative FAQ
How do I split comma delimited data in a SQL Statement?
|
Author's name: Frank Zhou Author's Email:zhou328@comcast.net |
Date written: 8th December 2005 Oracle version(s): 9.2 |
|
How do I split comma delimited data in a SQL Statement? |
The following SQL query pattern can be used to split comma delimited data into different rows.
SQL>
create table test1 as
select
'D1' AS DEPT , 'Simon,Jon,Frank' AS STR FROM DUAL
UNION ALL
select
'D2' AS DEPT , 'Peter,Adam' AS STR FROM DUAL
UNION ALL
select
'D3' AS DEPT , 'Tom,Lee,Jack' AS STR FROM DUAL;
SQL> select * from test1;
DE STR
-- ---------------
D1 Simon,Jon,Frank
D2 Peter,Adam
D3 Tom,Lee,Jack
SELECT DEPT,
SUBSTR(str,
INSTR(str, ',', 1, LEVEL ) + 1,
INSTR(str, ',', 1, LEVEL+1) -
INSTR(str, ',', 1, LEVEL) -1 ) name
FROM (
SELECT ','||str||',' AS str ,dept
FROM test1
)
CONNECT BY PRIOR DEPT = DEPT
AND INSTR (str, ',', 1, LEVEL+1) > 0
AND PRIOR dbms_random.string
('p', 10) IS NOT NULL;
DE NAME
-- ---------
D1 Simon
D1 Jon
D1 Frank
D2 Peter
D2 Adam
D3 Tom
D3 Lee
D3 Jack
8 rows selected.
SQL> spool off;
