Home » SQL & PL/SQL » SQL & PL/SQL » regexp_replace comma separated whole string (Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production)
| regexp_replace comma separated whole string [message #637484] |
Mon, 18 May 2015 13:37  |
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
|
Senior Member |
|
|
Hi,
Can you please help me with below:
Input : STAGE, HLD_REASON,STAGE, STAGE_STEP_OWNER,STAGE
Output: ABC,HLD_REASON,ABC,STAGE_STEP_OWNER,ABC
I tried below:
SELECT REGEXP_REPLACE ('STAGE, HLD_REASON,STAGE, STAGE_STEP_OWNER,STAGE',
'STAGE,|,STAGE,|,STAGE$',
'ABC')
FROM DUAL;
The above not helping.
Rule: Given input string will be in csv format. If it find whole string to replace, then only replace the string.
As using replace function, if I will try to replace STAGE with ABC, then it will convert STAGE_STEP_OWNER to ABC_STEP_OWNER, which is not correct. STAGE as whole word should be replaced.
Thanks,
Manu
|
|
|
|
|
|
|
|
| Re: regexp_replace comma separated whole string [message #637487 is a reply to message #637486] |
Mon, 18 May 2015 13:59  |
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
|
Senior Member |
|
|
Input : STAGE, HLD_REASON,STAGE, STAGE_STEP_OWNER,STAGE
StringToReplace : STAGE
Output: ABC,HLD_REASON,ABC,STAGE_STEP_OWNER,ABC
|
|
|
|
|
|
| Re: regexp_replace comma separated whole string [message #637489 is a reply to message #637487] |
Mon, 18 May 2015 14:02 |
 |
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
SQL> with data as (select 'STAGE, HLD_REASON,STAGE, STAGE_STEP_OWNER,STAGE' val from dual)
2 select regexp_replace(val, '(,?) *STAGE *(,?)', '\1ABC\2') res
3 from data
4 /
RES
---------------------------------------
ABC, HLD_REASON,ABC,ABC_STEP_OWNER,ABC
|
|
|
|
| Re: regexp_replace comma separated whole string [message #637490 is a reply to message #637489] |
Mon, 18 May 2015 14:17 |
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
|
Senior Member |
|
|
Thanks Michel, but the required output differs.
The output from your query is:
ABC, HLD_REASON,ABC,ABC_STEP_OWNER,ABC
The output expected is:
ABC,HLD_REASON,ABC,STAGE_STEP_OWNER,ABC
So the STAGE as whole word should be replace, now (STAGE) in (STAGE_STEP_OWNER) is partial word.
A whole word can be identified by either beginning of the string followed by a comma, or command and the word at end of the string, or between 2 commas, or just the word if it's the only word in comma separated string.
So all the stage occurrences should be replace, which are like below:
Input ---> Outupt
=================
STAGE --> ABC
STAGE,MNO,STAGE_STEP_OWNER --> ABC,MNO,STAGE_STEP_OWNER
MNO,STAGE,STAGE_STEP_OWNER --> MNO,ABC,STAGE_STEP_OWNER
MNO,STAGE_STEP_OWNER,STAGE --> MNO,STAGE_STEP_OWNER,ABC
STAGE,HLD_REASON,STAGE,STAGE_STEP_OWNER,STAGE --> ABC,HLD_REASON,ABC,STAGE_STEP_OWNER,ABC
Thanks,
Manu
[Updated on: Mon, 18 May 2015 14:26] Report message to a moderator |
|
|
|
|
|
| Re: regexp_replace comma separated whole string [message #637492 is a reply to message #637491] |
Mon, 18 May 2015 14:35 |
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
|
Senior Member |
|
|
WITH data
AS (SELECT 'STAGE' val, 'ABC' expected_res FROM DUAL
UNION
SELECT 'STAGE,MNO,STAGE_STEP_OWNER', 'ABC,MNO,STAGE_STEP_OWNER'
FROM DUAL
UNION
SELECT 'MNO,STAGE,STAGE_STEP_OWNER', 'MNO,ABC,STAGE_STEP_OWNER'
FROM DUAL
UNION
SELECT 'MNO,STAGE_STEP_OWNER,STAGE', 'MNO,STAGE_STEP_OWNER,ABC'
FROM DUAL
UNION
SELECT 'STAGE,HLD_REASON,STAGE,STAGE_STEP_OWNER,STAGE',
'ABC,HLD_REASON,ABC,STAGE_STEP_OWNER,ABC'
FROM DUAL)
SELECT val,
REGEXP_REPLACE (val, '(,?) *STAGE *(,?)', '\1ABC\2') res,
expected_res
FROM data; |
|
|
|
| Re: regexp_replace comma separated whole string [message #637493 is a reply to message #637492] |
Mon, 18 May 2015 14:44 |
|
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
SQL> WITH data
2 AS (SELECT 'STAGE' val, 'ABC' expected_res FROM DUAL
3 UNION
4 SELECT 'STAGE,MNO,STAGE_STEP_OWNER', 'ABC,MNO,STAGE_STEP_OWNER'
5 FROM DUAL
6 UNION
7 SELECT 'MNO,STAGE,STAGE_STEP_OWNER', 'MNO,ABC,STAGE_STEP_OWNER'
8 FROM DUAL
9 UNION
10 SELECT 'MNO,STAGE_STEP_OWNER,STAGE', 'MNO,STAGE_STEP_OWNER,ABC'
11 FROM DUAL
12 UNION
13 SELECT 'STAGE,HLD_REASON,STAGE,STAGE_STEP_OWNER,STAGE',
14 'ABC,HLD_REASON,ABC,STAGE_STEP_OWNER,ABC'
15 FROM DUAL)
16 SELECT val,
17 regexp_replace(val, '(^|,) *STAGE *(,|$)', '\1ABC\2') res,
18 expected_res
19 from data
20 /
VAL RES EXPECTED_RES
--------------------------------------------- --------------------------------------- ---------------------------------------
MNO,STAGE,STAGE_STEP_OWNER MNO,ABC,STAGE_STEP_OWNER MNO,ABC,STAGE_STEP_OWNER
MNO,STAGE_STEP_OWNER,STAGE MNO,STAGE_STEP_OWNER,ABC MNO,STAGE_STEP_OWNER,ABC
STAGE ABC ABC
STAGE,HLD_REASON,STAGE,STAGE_STEP_OWNER,STAGE ABC,HLD_REASON,ABC,STAGE_STEP_OWNER,ABC ABC,HLD_REASON,ABC,STAGE_STEP_OWNER,ABC
STAGE,MNO,STAGE_STEP_OWNER ABC,MNO,STAGE_STEP_OWNER ABC,MNO,STAGE_STEP_OWNER
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Apr 26 13:08:01 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
