Skip to Main Content
  • Questions
  • Extract multiple hostname from a long string by using regexp_substr

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Anders.

Asked: April 07, 2021 - 4:52 am UTC

Last updated: April 07, 2021 - 7:50 am UTC

Version: 11.2.0.4

Viewed 100+ times

You Asked

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

and we said...

Check this link

https://asktom.oracle.com/pls/apex/asktom.search?tag=convert-comma-separated-values-in-a-column-into-rows-and-join-the-result-set-with-another-table

for how we use regexp to split a string based on commas (ie, CSV to rows).

It might seem relevant, but the concept is - ie, we find a match (a comma or in your case a HOST), and then iterate "n" times using connect-from-dual to pick up the first, second, etc matching element.

Rating

  (1 rating)

Comments

Regexp

Rajeshwaran Jeyabal, April 08, 2021 - 2:47 pm UTC

demo@XEPDB1> variable x varchar2(300)
demo@XEPDB1> exec :x := '(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) ) (CONN
ECT_DATA = (SERVICE_NAME = Test) ) ))'

PL/SQL procedure successfully completed.

demo@XEPDB1>
demo@XEPDB1> col x for a25
demo@XEPDB1> select regexp_substr( :x,'HOST = (\S+)\)',1,level,'i',1) x
  2  from dual
  3  connect by level <= regexp_count(:x,'HOST = \S+\)');

X
-------------------------
lxc01-vip.test.com
lxc02-vip.test.com

demo@XEPDB1>


then finally apply listagg on top of it.

demo@XEPDB1> select listagg(x,' ') within group( order by n) x
  2  from (
  3  select regexp_substr( :x,'HOST = (\S+)\)',1,level,'i',1) x,level n
  4  from dual
  5  connect by level <= regexp_count(:x,'HOST = \S+\)')
  6     )
  7  /

X
--------------------------------------------------
lxc01-vip.test.com lxc02-vip.test.com

More to Explore

PL/SQL

Check out more PL/SQL tutorials on our LiveSQL tool.