• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 17154
  • Last Modified:

I want to replace multiple matching patterns with multiple specified replacement strings

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:

123abc...""

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:

select
case
when "big_text_column" like '%&%' then replace("big_text_column",  '&'   ,    '99')
when "big_text_column" like '%quot;%'   then replace("big_text_column",  'quot;'     ,     '88')
   else "big_text_column"
   end as "big_text_column"
from table_with_big_text_column;

The result set 'fixes' the & ones but does nada for the quot; replacement;  
   123abc...99quot;99quot;

I would like it to return:
     123abc...99889988

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.
0
egarrison
Asked:
egarrison
  • 3
  • 2
1 Solution
 
sathyagiriCommented:
Try this
select replace(replace(big_text_column,'&amp','99'),'quot',88) from table_with_big_text_column;
0
 
sathyagiriCommented:
Her's an example

SQL> set define off
SQL> select replace(replace('123abc...&ampquot;&ampquot','&amp','99'),'quot','88') from dual;

REPLACE(REPLACE('1
------------------
123abc...9988;9988
0
 
egarrisonCEO / CTOAuthor Commented:
Thank you, thank you. I didn't expect and answer so soon. Works great.

A side note suppose I wanted to subsitute '&amp' with  a single quote?
Instead of 99
    select replace(replace('123abc...&ampquot;&ampquot','&amp','99'),'quot','88') from dual;

It returns '
    select replace(replace('123abc...&ampquot;&ampquot','&amp',' ' '),'quot','88') from dual;
 
 But the SQL errors at the 3 ' i row and the ESCAPE clause did not work I guess it is only
for wildcard characters and not reserved characters like single quote?
0
 
sathyagiriCommented:
Try this
select replace(replace('123abc...&ampquot;&ampquot','&amp',''''),'quot','88') from dual
0
 
egarrisonCEO / CTOAuthor Commented:
Thank you, yep right on again...I guess I've been staring at this too long
as I had too many single quotes in my replacement sting for occurs of  '&amp'.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now