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.