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 ?