OraFAQ Forum: SQL & PL/SQL » Doubt in parsing expression using REGEXP

Home » SQL & PL/SQL » SQL & PL/SQL » Doubt in parsing expression using REGEXP_REPLACE

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Doubt in parsing expression using REGEXP_REPLACE [message #296833]

Tue, 29 January 2008 03:47

rolex.mp
Messages: 161
Registered: February 2007

Senior Member

I am using Oracle 10g Release 2 database .

I need to update a column in a table by parsing another column in the same table .

The table looks like this

SQL> desc ad_services
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------
 TG_ID                                     NOT NULL NUMBER
 NET_SERVICE_NAME                          NOT NULL VARCHAR2(64)
 SERVICE_NAME                                       VARCHAR2(64)
 DB_STRING                                 NOT NULL VARCHAR2(2000)

The column I have to parse is DB_STRING and the column that has to be populated is SERVICE_NAME

DB_STRING sample value will be like

'(DESCRIPTION=(ADDRESS = (PROTOCOL = TCP)(HOST = adc60001ftmr-vip)(PORT = 1524)) 
(ADDRESS = (PROTOCOL = TCP)(HOST = adc60001ftmr-vip)(PORT = 1521)) 
(ADDRESS = (PROTOCOL = TCP)(HOST = adc60002ftmr-vip)(PORT = 1524)) 
(ADDRESS = (PROTOCOL = TCP)(HOST = adc60002ftmr-vip)(PORT = 1521)) 
(LOAD_BALANCE = yes) (CONNECT_DATA=(SERVER = DEDICATED)(SERVICE_NAME = inst2.oracle.com)
(CID=(PROGRAM=adworker)(HOST=adc60027fems)(USER=app1780))
(CID=(PROGRAM=adworker)(HOST=adc60027fems)(USER=app1780))))'

From this string I have to pick the service name which is 'INST2' and populate it into SERVICE_NAME column

I am using SQL Query

update ad_tg_services ats1
        set ats1.service_name=
                (select
                        trim(regexp_replace(ats2.db_string,'.*(SERVICE_NAME.*=)(.*)(\..*)(\..*)','\2')) sname
                        from ad_tg_services ats2
                        where ats2.tg_id=p_tg_id and ats2.net_service_name=ats1.net_service_name)
        where tg_id=p_tg_id;

This query solves the problem but this query can further be simplified by eliminating TRIM function , in that case I tried using [:ALNUM:] inside REGEXP but the query was producing false results .This was the query I tried to get rid of TRIM

select regexp_replace(db_string,'.*(SERVICE_NAME.*=).*([_[:alnum:]]+)(\..*)','\2') sname
          from ad_tg_services 
          where tg_id=13
SQL> /

SNAME
-------------
e
e
e

Let me know how to accomplish this ?

Report message to a moderator

Re: Doubt in parsing expression using REGEXP_REPLACE [message #296858 is a reply to message #296833]

Tue, 29 January 2008 06:12

ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD

Senior Member

see oracle docs to how to use the regular_exp.

http://www.oracle.com/technology/oramag/webcolumns/2003/techarticles/rischert_regexp_pt1.html

Report message to a moderator

Re: Doubt in parsing expression using REGEXP_REPLACE [message #296983 is a reply to message #296858]

Tue, 29 January 2008 17:07

ebrian
Messages: 2794
Registered: April 2006

Senior Member

SQL> select string
  2         , regexp_replace(string, '.*SERVICE_NAME[ =]+([^.]+).*','\1') service_name
  3  from sn;

STRING                                                       SERVICE_NAME
------------------------------------------------------------ ------------
(DESCRIPTION=(ADDRESS = (PROTOCOL = TCP)(HOST = adc60001ftmr inst2
-vip)(PORT = 1524)) (ADDRESS = (PROTOCOL = TCP)(HOST = adc60
001ftmr-vip)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST
= adc60002ftmr-vip)(PORT = 1524)) (ADDRESS = (PROTOCOL = TCP
)(HOST = adc60002ftmr-vip)(PORT = 1521)) (LOAD_BALANCE = yes
) (CONNECT_DATA=(SERVER = DEDICATED)(SERVICE_NAME = inst2.or
acle.com)(CID=(PROGRAM=adworker)(HOST=adc60027fems)(USER=app
1780))(CID=(PROGRAM=adworker)(HOST=adc60027fems)(USER=app178
0))))

Report message to a moderator

Previous Topic:	Is this a good way of using table function?
Next Topic:	Sub-Queries , count table name n records

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Dec 01 11:12:58 CST 2024