REPLACE (TSQL Function)

Returns a string where all occurrences of the specified substring have been replaced with another value.

Syntax

REPLACE ( string_expression , string_to_replace , string_replacement )

Arguments

string_expression
is the string expression to search. It is of type varchar.
string_to_replace
all occurences of string_to_replace will be replaced with string_replacement. It is of type varchar.
string_replacement
is the string that will replace string_to_replace. It is of type varchar.

Return Types

Returns varchar.

The collation of the returned string is the same as string_expression,

Remarks

The collation used for string comparison depends on the collations of string_expression and string_to_replace, similar to the case of the = equality operator.
You can use the COLLATE clause to force an explicit collation to a string argument.

Examples

PRINT REPLACE('I ate an apple this morning.', 'apple', 'orange');
PRINT REPLACE('I ate an apple this morning.', 'APPLE', 'orange');

The result is:

I ate an orange this morning.
I ate an orange this morning.

Force a case sensitive and accent sensitive search by using COLLATE clause on an input string:

-- collation of the result string is same as first argument, which is probably the expected behaviour --
PRINT REPLACE('I ate an apple and an APPLE this morning.', 'APPLE' COLLATE en_cs_as, 'orange');

-- collation of the result string is forced to en_cs_as --
PRINT REPLACE('I ate an apple and an APPLE this morning.' COLLATE en_cs_as, 'APPLE', 'orange');

If you need to force a collation for the REPLACE function, it is often better to attach it to the string_to_replace argument. This way, the original collation of string_expression will propagate to the result string. which is probably the expected behaviour.

The result is:

I ate an apple and an orange this morning.
I ate an apple and an orange this morning.