Regular Expressions

REGEXP_REPLACE

The REGEXP_REPLACE function is similar to both the REPLACE function and the TRANSLATE function except that it replaces a string pattern, specified with a regular expression, instead of a string literal. As well, REGEXP_REPLACE allows you to specify backreferences (references to sub-patterns defined in the main search pattern) for the replacement string whereas REPLACE only allows string literals in the replacement string. The following examples compare replacements performed using REPLACE or TRANSLATE with those using REGEXP_REPLACE.

Before proceeding however, the following code will set up some test data for us (see Setup if you want to create the SET_TARGET and ADD_TARGET procedures on your own test system).

execute set_target( 'aaa'  )

execute add_target( 'abc'  )
execute add_target( 'bbb'  )
execute add_target( 'bcd'  )
execute add_target( 'cde'  )
execute add_target( 'def'  )
execute add_target( 'efg'  )

column target heading "TARGET|STRING"
 

REPLACE and TRANSLATE vs REGEXP_REPLACE

Replacing a string literal is performed as follows.
select
  target ,
  REPLACE( target, 'bc', 'XX' )
    as replaced_target
from
  targets
;
 
TARGET
STRING     REPLACED_TARGET
---------- ---------------
aaa        aaa
abc        aXX
bbb        bbb
bcd        XXd
cde        cde
def        def
efg        efg
 
select
  target ,
  REGEXP_REPLACE( target, 'bc', 'XX' )
    as replaced_target
from
  targets
;
 
TARGET
STRING     REPLACED_TARGET
---------- ---------------
aaa        aaa
abc        aXX
bbb        bbb
bcd        XXd
cde        cde
def        def
efg        efg
 
The following example shows how to replace all of the "a", "c", and "f" characters in the target with an "X".
select
  target ,
  TRANSLATE
    ( target, 'acf', 'XXX' )
    as replaced_target
from
  targets
;
 
TARGET
STRING     REPLACED_TARGET
---------- ---------------
aaa        XXX
abc        XbX
bbb        bbb
bcd        bXd
cde        Xde
def        deX
efg        eXg
 
select
  target ,
  REGEXP_REPLACE
    ( target, '[acf]', 'X' )
    as replaced_target
from
  targets
;
 
TARGET
STRING     REPLACED_TARGET
---------- ---------------
aaa        XXX
abc        XbX
bbb        bbb
bcd        bXd
cde        Xde
def        deX
efg        eXg
 
While the TRANSLATE function can perform many different single character replacements all at once, this cannot be done with a single, basic REGEXP_REPLACE expression.
select
  target ,
  TRANSLATE
    ( target, 'acf', 'XYZ' )
    as replaced_target
from
  targets
;
 
TARGET
STRING     REPLACED_TARGET
---------- ---------------
aaa        XXX
abc        XbY
bbb        bbb
bcd        bYd
cde        Yde
def        deZ
efg        eZg
 
--
-- not possible with REGEXP_REPLACE
--
 
Matching a simple search pattern, like a "bc" at the end of a string, cannot be easily performed with a single, basic REPLACE or TRANSLATE expression. REGEXP_REPLACE handles such pattern matches easily.
--
-- not possible with
-- REPLACE or TRANSLATE
--
 
select
  target ,
  REGEXP_REPLACE( target, 'bc$', 'XY' )
    as replaced_target
from
  targets
;
 
TARGET
STRING     REPLACED_TARGET
---------- ---------------
aaa        aaa
abc        aXY
bbb        bbb
bcd        bcd
cde        cde
def        def
efg        efg
 

To learn more about regular expression patterns in general see Pattern Matching.




Linking to SQL Snippets ™

To link to this page in Oracle Technology Network Forums or OraFAQ Forums cut and paste this code.

  • [url=http://www.sqlsnippets.com/en/topic-11744.html]SQL Snippets: Regular Expressions - REGEXP_REPLACE[/url]

To link to this page in HTML documents or Blogger comments cut and paste this code.

  • <a href="http://www.sqlsnippets.com/en/topic-11744.html">SQL Snippets: Regular Expressions - REGEXP_REPLACE</a>

To link to this page in other web sites use the following values.

  • Link Text : SQL Snippets: Regular Expressions - REGEXP_REPLACE
  • URL (href): http://www.sqlsnippets.com/en/topic-11744.html