SQL Server REPLACE() Function

The SQL Server (Transact-SQL) REPLACE() function replaces all occurrences of a specified substring within a string, with a new substring.

Syntax

REPLACE(string, from_substring, to_substring)

Parameters

Return Value

Returns the string with the replaced values.

Example 1:

The example below shows the usage of REPLACE() function.

SELECT REPLACE('SQL Tutorial', 'SQL', 'HTML');
Result: 'HTML Tutorial'

SELECT REPLACE('SQL Tutorial', 'sql', 'HTML');
Result: 'HTML Tutorial'

SELECT REPLACE('xyz xyz', 'xyz', 'abc');
Result: 'abc abc'

SELECT REPLACE('xyz xyz', 'XYZ', 'abc');
Result: 'abc abc'

SELECT REPLACE('100', '0', '5');
Result: '155'

SELECT REPLACE(100, 0, 5);
Result: '155'

Example 2:

Consider a database table called Employee with the following records:

In the query below, the REPLACE() function is used to create a new column called NewEmpID by replacing 'AXZ' with 'XAY' from all records of EmpID column value.

SELECT *, REPLACE(EmpID, 'AXZ', 'XAY') AS NewEmpID FROM Employee;

This will produce the result as shown below: