query help

[ebrian]

SQL> select * from hosts;

HOSTID
-----------------------------------------------------------------------------
host1:12.134.23.45,host2:34.56.76.102

SQL> with t as (
  2       select ltrim(regexp_substr(hostid, '[^,]+', 1, level), ',') str
  3       from hosts
  4       connect by level <= length(hostid)-length(replace(hostid,':')))
  5    select regexp_substr(str, '[^:]+') hostname
  6         , regexp_substr(str, '[^:]+$') ip
  7  from t;

HOSTNAME   IP
---------- -------------------
host1      12.134.23.45
host2      34.56.76.102