hi,

I am trying to modify a query ( which i got from internet searching) to my requirement.

The below query works fine for string '1:3,4:6,8:10,3:4,7:6,11:12
but i am looking for a string of this format,
host1:12.134.23.45,host2:34.56.76.102,..................

I am not familiar with regexp_substr. But still i am working on this.

If somebody can help me, i would greatly appreciate that.

Thanks
sonia





---------------------------------------------

declare
z varchar2(40);
cursor get_vals is
with t as (
select '1:3,4:6,8:10,3:4,7:6,11:12' z from dual
)
select
rtrim(regexp_substr(z,'\d+:',1,level),':') first,
ltrim(regexp_substr(z,':\d+',1,level),':') second
from t
connect by level<=length(z)-length(replace(z,',')) + 1;
begin
for a in get_vals loop
insert into mytable
values (a.first, a.second);
end loop;
commit;
end;
/