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.