Home » SQL & PL/SQL » SQL & PL/SQL » Problems with REPLACE( overreplacing (TOAD/ORACLE)
Problems with REPLACE( overreplacing [message #576907] Sun, 10 February 2013 09:35 Go to next message
bws93222
Messages: 27
Registered: April 2009
Junior Member
How best to deal with the following REPLACE issue?
I want to replace a short substring but only when
it "stands alone"--in other words, say, "tom custom"
should be changed to "thomas custom" not "thomas custhomas"

I can do various conditions to avoid this type of overreplacement
but I'm not able to cover all scenarios. See the example below
in which the second MYSTRING is overreplaced.
What's the easiest way to get around this?

MYSTRING = 'findmenot find donotfind'

REPLACE (MYSTRING, ' find ', ' GOTCHA ') ==> 'findmenot GOTCHA donotfind' -- GOOD!!

...

MYSTRING = 'find donotfind findmenot'

REPLACE (MYSTRING, 'find ', 'GOTCHA ') ==> 'GOTCHA donotGOTCHA findmenot' -- NOT GOOD!!

[Updated on: Sun, 10 February 2013 09:37]

Report message to a moderator

Re: Problems with REPLACE( overreplacing [message #576910 is a reply to message #576907] Sun, 10 February 2013 09:59 Go to previous messageGo to next message
BlackSwan
Messages: 23150
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: Problems with REPLACE( overreplacing [message #576911 is a reply to message #576907] Sun, 10 February 2013 10:25 Go to previous messageGo to next message
Michel Cadot
Messages: 59993
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
With any SQL or PL/SQL question, please, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

And FEEDBACK to those that help you thing you did not do but for one topic; do you think we owe you answers and help and it is not worth to thank us and tell us if it solve your problem?

Regards
Michel

[Updated on: Sun, 10 February 2013 10:26]

Report message to a moderator

Re: Problems with REPLACE( overreplacing [message #576920 is a reply to message #576907] Sun, 10 February 2013 12:52 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2078
Registered: January 2010
Senior Member
Not that straight-forward. Assuming string can't contain character CHR(0):

with t as (
           select 'tom custom' str from dual
          )
select  replace(replace(chr(0) || replace(str,' ',chr(0) || ' ' || chr(0)),chr(0) || 'tom' || chr(0),'thomas'),chr(0))
  from  t
/

REPLACE(REPLA
-------------
thomas custom

SQL> 


SY.
Re: Problems with REPLACE( overreplacing [message #576921 is a reply to message #576920] Sun, 10 February 2013 13:03 Go to previous messageGo to next message
Littlefoot
Messages: 19889
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Something like this?
SQL> with test as
  2    (select 'findmenot find donotfind' col from dual union
  3     select 'find donotfind findmenot' from dual union
  4     select 'find' from dual union
  5     select 'findmenot find' from dual
  6    )
  7  select
  8    col,
  9    trim(regexp_replace(col, '(^find )|( find$)|( find )|(^find$)', ' GOTCHA ')) result
 10  from test;

COL                            RESULT
------------------------------ ------------------------------
find                           GOTCHA
find donotfind findmenot       GOTCHA donotfind findmenot
findmenot find                 findmenot GOTCHA
findmenot find donotfind       findmenot GOTCHA donotfind

SQL>
Re: Problems with REPLACE( overreplacing [message #576922 is a reply to message #576921] Sun, 10 February 2013 17:39 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2078
Registered: January 2010
Senior Member
Littlefoot wrote on Sun, 10 February 2013 14:03
Something like this?


Not exactly. Issue is single delimiter between two consecutive to-be-replaced words. And unfortunately Oracle regular expressions do not support look-behind:

with test as (
              select 'findmenot find find donotfind' col from dual union
              select 'find find donotfind findmenot' from dual union
              select 'find find' from dual union
              select 'findmenot find find' from dual
             )
select  col,
        trim(regexp_replace(col, '(^find )|( find$)|( find )|(^find$)', ' GOTCHA ')) result
  from test
/

COL                           RESULT
----------------------------- ---------------------------------
find find                     GOTCHA find
find find donotfind findmenot GOTCHA find donotfind findmenot
findmenot find find           findmenot GOTCHA find
findmenot find find donotfind findmenot GOTCHA find donotfind

SQL>


So, as I already mentioned, simplest way is to use character that can't appear in source string (assuminig there is one):

with test as (
              select 'findmenot find find donotfind' col from dual union
              select 'find find donotfind findmenot' from dual union
              select 'find find' from dual union
              select 'findmenot find find' from dual
             )
select  col,
        replace(
                replace(
                        chr(0) || replace(
                                          col,' ',
                                          chr(0) || ' ' || chr(0)
                                         ) || chr(0),
                        chr(0) || 'find' || chr(0),
                        'GOTCHA'
                       ),
                chr(0)
               ) result
  from  test
/

COL                           RESULT
----------------------------- -----------------------------------
find find                     GOTCHA GOTCHA
find find donotfind findmenot GOTCHA GOTCHA donotfind findmenot
findmenot find find           findmenot GOTCHA GOTCHA
findmenot find find donotfind findmenot GOTCHA GOTCHA donotfind

SQL>


SY.

[Updated on: Sun, 10 February 2013 17:47]

Report message to a moderator

Re: Problems with REPLACE( overreplacing [message #577089 is a reply to message #576922] Tue, 12 February 2013 04:20 Go to previous message
c_stenersen
Messages: 255
Registered: August 2007
Senior Member
Or if there's no such character available you can replace one space by two, and replace them back again afterwards

with test as (
              select 'findmenot find find donotfind' col from dual union
              select 'find find donotfind findmenot' from dual union
              select 'find find' from dual union
              select 'findmenot find find' from dual union
              select 'findmenot find donotfind' from dual
              
             )
select  col,
        trim(
             replace(
                 replace(
                         replace(' ' || col || ' ', ' ', '  '),
                         ' find ',
                         ' GOTCHA '),
                 '  ', 
                 ' ') 
             ) result
  from  test


COL                           RESULT
----------------------------- ---------------------------------
find find                     GOTCHA GOTCHA
find find donotfind findmenot GOTCHA GOTCHA donotfind findmenot
findmenot find donotfind      findmenot GOTCHA donotfind
findmenot find find           findmenot GOTCHA GOTCHA
findmenot find find donotfind findmenot GOTCHA GOTCHA donotfind
Previous Topic: Minus operator and NULL
Next Topic: Execute the expression in select statement
Goto Forum:
  


Current Time: Fri Dec 19 18:16:03 CST 2014

Total time taken to generate the page: 0.12766 seconds