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?

Back to index of questions


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;

 


 

Back to top

Back to index of questions