Code:
SQL> select * from hosts;

HOSTID
--------------------------------------------------------------------
host1:12.134.23.45,host2:IP34.56.76.102:1521,hosts3,hosts4:10.2.1.0,
12.3.4.3:3030,hosts5.local:198.162.10.1:1521,hosts6.local.com,host7:
3000


SQL> with t as (
  2           select regexp_substr(hostid, '[^,]+', 1, level) str
  3           from hosts
  4           connect by level <= length(hostid)-length(replace(hostid,','))+1)
  5  select regexp_substr(str, '[^:]*[a-z]+[^:]*',1,1,'i') hostname
  6  ,      regexp_substr(str, '\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}') ip
  7  from t;

HOSTNAME             IP
-------------------- --------------------
host1                12.134.23.45
host2                34.56.76.102
hosts3
hosts4               10.2.1.0
                     12.3.4.3
hosts5.local         198.162.10.1
hosts6.local.com
host7

8 rows selected.