Oracle 11g Release 2

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