Hi Tom,
I am trying to extract multiple hostname out from a long strings (In fact, I will use this method for DBLink - host column later). However, my query is only able to extract the first hostname. I did try to put {1,0} after the match pattern '((HOST|HOSTNAME)[[:space:]]*(\=)[[:space:]]*(\w+|\-|\_)*(\.\w+)*|(\w+|\-|\_)*(\.\w+)+|((\w+|\-|\_)+$)){1,}', but still not able to extract the rest of occurrence match at all. Can you please help me out? Thanks.
== The query output ===
HOSTNAME
--------------------
LXC01-VIP.TEST.COM
Expected Output
--------------------
LXC01-VIP.TEST.COM LXC02-VIP.TEST.COM
-------------------------------- My current query ----------------------------------
SELECT
REGEXP_REPLACE (
REGEXP_REPLACE (
REGEXP_REPLACE (
REGEXP_SUBSTR (
UPPER (
'(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = lxc01-vip.test.com)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = lxc02-vip.test.com)(PORT = 1521)) (LOAD_BALANCE = ON) (FAILOVER = ON) ) (CONNECT_DATA = (SERVICE_NAME = Test) ) )'),
'((HOST|HOSTNAME)[[:space:]]*(\=)[[:space:]]*(\w+|\-|\_)*(\.\w+)*|(\w+|\-|\_)*(\.\w+)+|((\w+|\-|\_)+$))'),
' ',
''),
'HOST=',
''),
'HOSTNAME=',
'')
HOSTNAME
FROM DUAL;
Regards,
Anders