We're taking a break this week as Connor and Chris will both be at CloudWorld, the premier Oracle conference of 2024. If you're in Vegas, please come say Hi or pop into our sessions

Thanks for the question, Anders.

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

Last updated: May 25, 2023 - 12:55 pm UTC

Version: 11.2.0.4

Viewed 10K+ times! This question is

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 Connor 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

(2 ratings)

We're not taking comments currently, so please try again later if you want to add a comment.

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

extract host_name from v$instance

A reader, May 25, 2023 - 6:09 am UTC

Hi

I want to extract only first field of fqdn which is listed in v$instance

like myhostname.somewhere.env.com is and I want output only myhostname using regexp

Please help

May 25, 2023 - 12:55 pm UTC

You mean like this?

select regexp_substr ( 'myhostname.somewhere.env.com', '^[^.]+' ) host
from   dual;

HOST      
----------
myhostname

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Regexp

extract host_name from v$instance

More to Explore

PL/SQL demos

PL/SQL docs

Extract multiple hostname from a long string by using regexp_substr

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Regexp

extract host_name from v$instance

More to Explore

PL/SQL demos

PL/SQL docs