Hi All,
I've the following table data:
create table tst_country(
country_id number primary key,
country_name varchar2(100));
insert into tst_country values(1,'Australia,Canada, United States, India');
insert into tst_country values(2,'Australia, Egypt, Virgin Islands,US.,Canada');
insert into tst_country values(3,'Australia, India, Virgin Islands, British,Jamaica');
select * from tst_country;
-- It displays 282 records.
Expected Result (8 records)
Australia
Canada
Egypt
India
Jamaica
United States
Virgin Islands, British
Virgin Islands,US.
Query
with rws as (
select country_id, country_name str from tst_country
)
select regexp_substr (
str,
'[^,]+',
1,
level
) value
from rws
connect by level <=
length ( str ) - length ( replace ( str, ',' ) ) + 1;
Notes
(1) The country "Virgin Islands,US." has a comma in it and after comma no space.
(2) The country "Virgin Islands, British" has a comma in it and lots of spaces after the comma.
(3) Countries are separated by comma with uneven spaces.
(4) The output should not display duplicate country names.
Thank you.