.png)
REPLACE (Transact-SQL)
Replaces all occurrences of a specified string value with another string value.
Transact-SQL Syntax Conventions
- string_expression1
-
Is the string expression to be searched. string_expression1 can be of a character or binary data type.
- string_expression2
-
Is the substring to be found. string_expression2 can be of a character or binary data type.
- string_expression3
-
Is the replacement string. string_expression3 can be of a character or binary data type.
REPLACE performs comparisons based on the collation of the input. To perform a comparison in a specified collation, you can use COLLATE to apply an explicit collation to the input.
The following example replaces the string cde in abcdefghi with xxx.
SELECT REPLACE('abcdefghicde','cde','xxx');
GO
Here is the result set.
------------ abxxxfghixxx (1 row(s) affected)
The following example uses the COLLATE function.
SELECT REPLACE('Das ist ein Test' COLLATE Latin1_General_BIN,
'Test', 'desk' );
GO
Here is the result set.
------------ Das ist ein desk (1 row(s) affected)
Reference
Data Types (Transact-SQL)String Functions (Transact-SQL)
