The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments, but we might still sneak in a few Office Hours sessions! Thanks for being a member of the AskTOM community.

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