How to replace multiple replace functions?

Recently in OTN, some one posted below question.

using 11.2.0.4 EE.
How can I replace the following:
“replace(replace(replace(config_item,’COLLATERAL’,’COLLAT’),
‘AGREEMENT’,’AGREE’),’VALUE’,’VAL’);”
with a single regular expression or something simpler ?

The reason is I need something simpler since more strings will be required to replace than simply the three currently listed.

Over here, OP wanted to replace multiple words in a string with different respective words.

As per my knowledge Regex_replace was not an options as it can’t replace multiple character with multiple character.
but can replace multiple character with single character!!

SELECT REGEXP_REPLACE('COLLATERAL and AGREEMENT and VALUE','COLLATERAL|AGREEMENT|VALUE', 'REPLACE')
from dual;

output :
REPLACE and REPLACE and REPLACE

Recursive With Clause was the solution proposed as below.

with alias1 as
(select rownum col1 , data1,data2
from (select 'COLLATERAL' data1, 'COLLAT' data2 from dual union all
select 'AGREEMENT'data1, 'AGREE' from dual union all
select 'VALUE'data1, 'VAL' from dual)) , --- get replace character
alias2 as
(select 'COLLATERAL and AGREEMENT and VALUE' col1 from dual), /* get complete string for replacement.*/
r(str,lvl) as
(
select REPLACE(col1,'COLLATERAL','COLLAT') str, 1 lvl from alias2
UNION ALL
SELECT REPLACE(str,alias1.data1,alias1.data2) str, lvl + 1
from alias2,r,alias1
where alias1.col1 = r.lvl
) -- Recursive with clause
select str from r where lvl = (select max(lvl) from r);

output :
COLLAT and AGREE and VAL

For more information on Recursive With clause(recursive subquery factoring), please check.

For alternative solutions and complete thread please refer below.
Click here

About Deepak Mahto

Having 8+ Years of relevant/exciting/fruitful/challenging/learning in Oracle Technology. Currently working as Oracle Performance Consultant in an MNC. Hold Expertise in Performance Engineering, SQL Tuning, Database Tuning, SQL, PLSQL Development/Design Considerations and Oracle Internals. I enjoy reading on Oracle internals, posting some of my finding or observations and playing football.
This entry was posted in Helper SQL, Oracle Internal and tagged , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s