REPLACE

Replaces all occurrences of a specified string value with another string value.

Syntax

REPLACE ( string_expression , string_pattern , string_replacement )

Arguments

string_expression

Is the string expression to be searched. string_expression can be of a character or binary data type.

_string_pattern_

Is the substring to be found. _string_pattern_ can be of a character or binary data type. _string_pattern_ cannot be an empty string (‘’), and must not exceed the maximum number of bytes that fit on a page.

_string_replacement_

Is the replacement string. _string_replacement_ can be of a character or binary data type.

Return types

Returns nvarchar if one of the input arguments is of the nvarchar data type; otherwise, REPLACE returns varchar.

Returns NULL if any one of the arguments is NULL.

If string_expression is not of type varchar(max) or nvarchar(max), REPLACE truncates the return value at 8,000 bytes. To return values greater than 8,000 bytes, string_expression must be explicitly cast to a large-value data type.

Examples

SELECT REPLACE('This is a Test', 'Test', 'desk');

See Also