REPLACE Function

The REPLACE function replaces specified characters within a source string with different characters.

REPLACE Function


Element	Description	Restrictions	Syntax
new_string	Character or characters that replace old_string in the return string	Must be an expression, constant, column, or host variable of a data type that can be converted to a character data type	Expression
old_string	Character or characters in source_string that are to be replaced by new_string	Must be an expression, constant, column, or host variable of a data type that can be converted to a character data type	Expression
source_string	String of characters argument to the REPLACE function	Must be an expression, constant, column, or host variable of a data type that can be converted to a character data type	Expression

Any argument to the REPLACE function must be of a built-in data type.

The REPLACE function returns a copy of source_string in which every occurrence of old_string is replaced by new_string. If you omit the new_string option, every occurrence of old_string is omitted from the return string.

The return data type is its source_string argument. If a host variable is the source, the return value is either NVARCHAR or NCHAR, according to the length of the returned string, using the return type promotion rules that the section Return Types from the CONCAT Function describes.

In the following example, the REPLACE function replaces every occurrence of xz in the source string with t:

SELECT REPLACE('Mighxzy xzime', 'xz', 't')
   FROM mytable;

The following table shows the output of this SELECT statement.


(constant)
`Mighty time`