I want to replace multiple matching patterns with multiple specified replacement strings
Posted on 2006-07-15
Oracle 10g R1
We have varchar2(250) columns that have various text data we want to replace with other specific text.
We have record as string like:
and we want to replace each & and each quot;
with 99 and 88 respectively.
I have tried REPLACE and REPLACE with CASE statements and we've tried REGEXP_REPLACE.
The problem seems that Oracle SQL will only replace the first one listed and ignores
any other 'find this and replace with this' after in the string.
Here is an example of the SQL:
when "big_text_column" like '%&%' then replace("big_text_column", '&' , '99')
when "big_text_column" like '%quot;%' then replace("big_text_column", 'quot;' , '88')
end as "big_text_column"
The result set 'fixes' the & ones but does nada for the quot; replacement;
I would like it to return:
How can I get it to change both (or more if needed) to their respective 99 and 88 ? We are not familiar with PL/SQL.