REPLACE (Transact-SQL)

Returns the string that results from replacing all occurrences of a specified substring within a given string with another specified substring.

REPLACE ( string_expression , string_pattern , string_replacement )

Arguments

string_expression
The expression that contains the string to be searched. Can be of character data type.
string_pattern
The expression that contains the substring to be replaced. Can be of character data type.
string_replacement
The expression that contains the replacement substring. Can be of character data type.

Return Type

Returns character data. The return type is determined by the type of string_expression.

Remarks

REPLACE performs a case-insensitive comparison by default, depending on the collation of the server or database.

If any of the arguments are NULL, REPLACE returns NULL.

If string_pattern is an empty string ('') REPLACE returns the string_expression without modification. This is consistent with the behavior of string replacement in many other operating systems.

REPLACE can be used within a SELECT statement.

Examples

A. Replacing a substring

The following example replaces all occurrences of 'abc' with '123' in the string 'This is abc, and that is abc too.':

SELECT REPLACE('This is abc, and that is abc too.', 'abc', '123');

Result:

'This is 123, and that is 123 too.'

B. Replacing a substring in a table column

The following example replaces all occurrences of 'Ltd.' with 'Limited' in the BusinessEntityDescription column of the Production.ProductDescription table:

USE AdventureWorks;
GO

SELECT
    ProductID,
    REPLACE(BusinessEntityDescription, 'Ltd.', 'Limited') AS UpdatedDescription
FROM
    Production.ProductDescription
WHERE
    BusinessEntityDescription LIKE '%Ltd.%';
GO

Note: This example requires the AdventureWorks sample database to be installed.