SQL Server REPLACE() Function: Replace String

In SQL Server, the REPLACE() function replaces all occurrences of the given string with the specified substring.

REPLACE(string_expression, search_pattern, stringToReplace)

Parameters:

string_expression: A string where the search_pattern should be searched.

search_pattern: A string pattern that should be searched in the string_expression and replaced. If a pattern is empty, then the string_expression is returned unchanged.

stringToReplace: A string that will replaced the search_pattern substring in the string_expression.

Return Value

Returns nvarchar if the input arguments are of nvarchar data type else returns varchar. Returns NULL if any of the arguments is NULL.

Example 1:

The following example demonstrates the REPLACE() function.

Example: REPLACE()

SELECT REPLACE('abcdefghabcdefgh','abcd','****') AS Result1,
REPLACE('abcd efg h abc de fgh','abcd','****') AS Result2;

Example 2:

In the following example, the word 'nice is replaced with the word 'good' in the input string 'Hello! Have a nice day.'

Example: REPLACE()

SELECT REPLACE('Hello! Have a nice day.', 'nice', 'good') AS Greetings;

Example 3:

In the following example, the Email column of the Employee table is the input. The REPLACE() function replaces any occurrence of 'abc.com' with '***.com'.

Example: REPLACE()

SELECT Email, REPLACE(EMail, 'abc.com', '***.com') AS DisplayEmail  FROM Employee;

Example 4

The REPLACE() function can also be used to update the column data of the table.

Consider the following Employee data where DepartmentID=60.

Now, the following updates the domain of all email to 'companyHR.com' using the REPLACE() function.

Example: REPLACE()

UPDATE Employee  
SET EMail = REPLACE(EMail, 'abc.com', 'companyHR.com')
WHERE DepartmentID = 60

The following is updated email addresses.