Split column value based on comma present in column

Question

I have a Table called Test with below values

ID Name Description
1  Xyz   Mumbai,Delhi

I want Sql query which will give output as below

ID Name Description
1  Xyz   Mumbai
1  Xyz   Delhi

Shame on whoever it was who stored comma separated data. – jarlh May 16 '19 at 11:44 — jarlh, May 16 '19 at 11:44
@jarlh could you please help me in getting solution of it ? – Ravi058 May 16 '19 at 11:49 — Ravi058, May 16 '19 at 11:49

score 0 · Accepted Answer · answered May 16 '19 at 11:58

You can use regexp_substr with '[^,]+' pattern as second argument to split the comma seperated string

with tab( ID, Name, Description ) as
(
 select 1,'Xyz','Mumbai,Delhi' from dual
)
select ID, Name, 
       regexp_substr(Description,'[^,]+',1,level)
       as Description
  from tab 
 connect by level <= regexp_count(Description,',')+1;

ID  NAME   DESCRIPTION
--  ----   -----------
1   Xyz    Mumbai
1   Xyz    Delhi

Split column value based on comma present in column

1 Answers1