By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table comma_seperated (id, name, city) as
select 1, 'RAJ', 'CHENNAI, HYDERABAD, JABALPUR' from dual
union all select 2, 'SAM', 'BHOPAL,PUNE' from dual;
2 rows affected
create or replace function split_function (p_string varchar2)
return sys.odcivarchar2list pipelined as
begin
for r in (
select result
from xmltable (
'if (contains($X,",")) then ora:tokenize($X,"\,") else $X'
passing p_string as x
columns result varchar2(4000) path '.'
)
)
loop
pipe row (trim(r.result));
end loop;
end split_function;
/
select id, split_function(city) from comma_seperated;
select cs.id, t.column_value as city
from comma_seperated cs
cross join table(split_function(cs.city)) t;
| ID | CITY |
|---|---|
| 1 | CHENNAI |
| 1 | HYDERABAD |
| 1 | JABALPUR |
| 2 | BHOPAL |
| 2 | PUNE |