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

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL

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