Export (0) Print
Expand All
Expand Minimize

REPLACE (Transact-SQL)

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

Topic link icon Transact-SQL Syntax Conventions


REPLACE ( string_expression1 , string_expression2 , string_expression3 )

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.

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.

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)

Community Additions

ADD
Show:
© 2014 Microsoft