Home » SQL & PL/SQL » SQL & PL/SQL » Doubt in parsing expression using REGEXP_REPLACE
Doubt in parsing expression using REGEXP_REPLACE [message #296833] Tue, 29 January 2008 03:47 Go to next message
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 ?
Re: Doubt in parsing expression using REGEXP_REPLACE [message #296858 is a reply to message #296833] Tue, 29 January 2008 06:12 Go to previous messageGo to next message
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
Re: Doubt in parsing expression using REGEXP_REPLACE [message #296983 is a reply to message #296858] Tue, 29 January 2008 17:07 Go to previous message
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))))
Previous Topic: Is this a good way of using table function?
Next Topic: Sub-Queries , count table name n records
Goto Forum:
  


Current Time: Sun Dec 11 06:24:03 CST 2016

Total time taken to generate the page: 0.07516 seconds