Home Library Learn Downloads Support Community
Sign in | United States - English | |
21 out of 30 rated this helpful Rate this topic

REPLACE (Transact-SQL)

SQL Server 2008 R2

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

Topic link icon Transact-SQL Syntax Conventions

Copy 

REPLACE ( string_expression , string_pattern , string_replacement )
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 ('').

string_replacement

Is the replacement string. string_replacement 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.

Copy 
SELECT REPLACE('abcdefghicde','cde','xxx');
GO

Here is the result set.

Copy 
------------
abxxxfghixxx
(1 row(s) affected)

The following example uses the COLLATE function.

Copy 
SELECT    REPLACE('Das ist ein Test'  COLLATE Latin1_General_BIN,
'Test', 'desk' );
GO

Here is the result set.

Copy 
------------
Das ist ein desk
(1 row(s) affected)
Did you find this helpful?
(2000 characters remaining)
Community Content Add
Annotations FAQ
Replace Group of Characters
@original - You can use regex instead of nesting your REPLACE's.

CREATE FUNCTION [dbo].[removeStringCharacters]
/*
    Author: Cameron Usman
    Date: 2011-09-13

    UDF Fuction that returns passed in @string value after removing any characters found in @remove
        @remove can be anything that can be contained within [@remove] to be used as regex

    Sample Call
        SELECT dbo.removeStringCharacters('ABCD.1234,zyxw-0_987*','.,-_*')
*/
(
 @string varchar(max)
 , @remove varchar(100)
)
RETURNS varchar(max)
AS
BEGIN
    WHILE @string LIKE '%[' + @remove + ']%'
    BEGIN
        SET @string = REPLACE(@string,SUBSTRING(@string,PATINDEX('%[' + @remove + ']%',@string),1),'')
    END

    RETURN @string
END
History