Replaces all occurrences of string_exp2 in string_exp1 with string_exp3.

Syntax

REPLACE ( string_exp1 , string_exp2 , string_exp3 )

Notes

  • string_exp can be fixed‑length or variable‑length CHARACTER data types.
  • If any of the arguments of the expression evaluates to NULL, the result is NULL.
  • If the replacement string is not found in the search string, it returns the original string.
  • Each occurrence of string_exp and the result can contain multi‑byte characters. Character comparisons are case sensitive and are determined by sort weights in the collation table in the database.

Example

This example illustrates the REPLACE function, replacing the letters ‘mi' in the last_name ‘Smith' with the letters ‘moo':

SELECT REPLACE ( last_name,'mi','moo' )
     FROM customer WHERE last_name = 'Smith';
 REPLACE(LAST_NAME,MI,MOO)
-------------------------
Smooth                   
 1 record selected

Compatibility

ODBC compatible