Link to home
Start Free TrialLog in
Avatar of WARTELLE
WARTELLEFlag for France

asked on

How to replace in ORACLE a substring with another substring in a UPDATE/REPLACE statement

I need to REPLACE a substring 'fi' by another (i.e. 'fi') in a VARCHAR2 field
The pb for this REPLACE is that Oracle wants to interpret the string as a substitution...
How do you bypass the substitution to be interpreted as literal characters ?

I use Oracle 9i

Thanks for your help
Vincent
Substr1 = 'fi'
Substr2 = 'fi'
 
UPDATE FACILCATPRODITEM SET item_desc = REPLACE(item_desc, 'Substr1', 'Substr2') 
  WHERE dbid = '#variables.iDBID#' 
    AND cat_id='#variables.iCatID#'
    AND prod_id='#variables.iProdID#'
    AND item_xml_tagname = '#variables.XML_TagName#'
    AND item_langue = '#variables.CAT_LANGUE#'
    AND item_actif = '1'
    AND item_type = 'TXT'

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Muhammad Khan
Muhammad Khan
Flag of Canada image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Muhammad Khan
Muhammad Khan
Flag of Canada image
Other methods:

Define an escape character:

SET ESCAPE '\'
SELECT '\&abc' FROM dual;

Don't scan for substitution variables:

SET SCAN OFF
SELECT '&ABC' x FROM dual;