REPLACE Function

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

1  REPLACE ( source_string , old_string?  , new_string )
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