REPLACE

REPLACE is a SQL function that scans through a string, replacing one string with another. If no other string is specified, it removes the string specified in the replacement string parameter.

The syntax is:

replace( string1, string_to_replace, [ replacement_string ] );

Examples

Replace "am" with "am not":

SELECT replace('I am here', 'am', 'am not') FROM dual;

Warning

Note that the REPLACE function works with a 4000 bytes buffer. If your database character set is a multibyte character set, this can lead to some issues:

SQL> create table t (col varchar2(3000 char));
SQL> insert into t values (lpad('ee',3000,'àà'));
SQL> select * from t;
COL
------------------------------------------------------------
àààààààààààààààààààààààààààààààààààààààààààààààààààààààààààà
àààààààààààààààààààààààààààààààààààààààààààààààààààààààààààà
àààààààààààààààààààààààààààààààààààààààààààààààààààààààààààà
<snip>
àààààààààààààààààààààààààààààààààààààààààààààààààààààààààààà
àààààààààààààààààààààààààààààààààààààààààààààààààààààààààààà
àààààààààààààààààààee

SQL> select length(col),lengthb(col) from t;
LENGTH(COL) LENGTHB(COL)
----------- ------------
       2001         4000

SQL> update t set col=replace(col,'e','é');
SQL> select * from t;
COL
------------------------------------------------------------
àààààààààààààààààààààààààààààààààààààààààààààààààààààààààààà
àààààààààààààààààààààààààààààààààààààààààààààààààààààààààààà
àààààààààààààààààààààààààààààààààààààààààààààààààààààààààààà
<snip>
àààààààààààààààààààààààààààààààààààààààààààààààààààààààààààà
àààààààààààààààààààààààààààààààààààààààààààààààààààààààààààà
àààààààààààààààààààé

SQL> select length(col),lengthb(col) from t;
LENGTH(COL) LENGTHB(COL)
----------- ------------
       2000         4000

The 'à' and 'é' characters take 2 bytes whereas 'e' takes only one. As you can see, replacing all 'e' with 'é' remove one character from the value due to a silent internal buffer overflow.

Also see

• REGEXP REPLACE, regular expression replace function.
• TRANSLATE, replace characters in a string, one char at a time.