| Problems with REPLACE( overreplacing [message #576907] |
Sun, 10 February 2013 09:35  |
bws93222
Messages: 13
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 #576911 is a reply to message #576907] |
Sun, 10 February 2013 10:25  |
 |
Michel Cadot
Messages: 54155
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 |
Solomon Yakobson
Messages: 1398
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 #576922 is a reply to message #576921] |
Sun, 10 February 2013 17:39 |
Solomon Yakobson
Messages: 1398
Registered: January 2010
|
Senior Member |
|
|
Littlefoot wrote on Sun, 10 February 2013 14:03Something 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 |
c_stenersen
Messages: 253
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 |
|
|
|
] 
Members
Search
Help
Register
Login
Home
