Product Documentation

SQL Reference Guide

Previous Topic

Next Topic

REPLACE function (ODBC compatible)


REPLACE ( string_exp1,string_exp2,string_exp3 )


The scalar function REPLACE replaces all occurrences of string_exp2 in string_exp1 with string_exp3. The resultant character string will have the same character set as that of the arguments.


SELECT REPLACE ( name,'mi','moo' )

FROM customer

WHERE name = 'Smith';



1 record selected


  • string_exp can be any of the type fixed or variable length character types.
  • If any of the arguments of the expression evaluates to null, the result is null.
  • If one of the arguments is a literal and the other one a field reference, the operation is possible only if the literal is convertible to the character set of the field reference. Otherwise an error is returned.
  • If the replacement string is not found in the search string, it returns the original string.