Solved

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

Posted on 2006-07-15
5
13,756 Views
Last Modified: 2007-12-19
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
Comment
Question by:egarrison
  • 3
  • 2
5 Comments
 
LVL 14

Expert Comment

by:sathyagiri
ID: 17115138
Try this
select replace(replace(big_text_column,'&amp','99'),'quot',88) from table_with_big_text_column;
0
 
LVL 14

Accepted Solution

by:
sathyagiri earned 500 total points
ID: 17115157
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
 
LVL 3

Author Comment

by:egarrison
ID: 17115344
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
 
LVL 14

Expert Comment

by:sathyagiri
ID: 17115433
Try this
select replace(replace('123abc...&ampquot;&ampquot','&amp',''''),'quot','88') from dual
0
 
LVL 3

Author Comment

by:egarrison
ID: 17115476
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

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
exp/imp 25 98
use lov values 2 71
Oracle Distributed Transaction Lock Error ORA-01591 8 65
oracle differnce between two timestamps 5 38
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question