Recently in OTN, some one posted below question.
using 220.127.116.11 EE.
How can I replace the following:
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')
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
(select 'COLLATERAL and AGREEMENT and VALUE' col1 from dual), /* get complete string for replacement.*/
select REPLACE(col1,'COLLATERAL','COLLAT') str, 1 lvl from alias2
SELECT REPLACE(str,alias1.data1,alias1.data2) str, lvl + 1
where alias1.col1 = r.lvl
) -- Recursive with clause
select str from r where lvl = (select max(lvl) from r);
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.