T-SQL Tutorial T-SQL Advanced Database Management T-SQL References

T-SQL REPLACE() Function



The T-SQL (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

string Required. Specify the source string.
from_substring Required. Specify the substring to be replaced. All occurrences of from_substring found within string will be replaced with to_substring.
to_substring Required. Specify the replacement substring. All occurrences of from_substring found within string will be replaced with to_substring.

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:

EmpIDNameCity
AXZ1JohnLondon
AXZ2MarryNew York
AXZ3JoParis
AXZ4KimAmsterdam
AXZ5RameshNew Delhi
AXZ6HuangBeijing

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:

EmpIDNameCityNewEmpID
AXZ1JohnLondonXAY1
AXZ2MarryNew YorkXAY2
AXZ3JoParisXAY3
AXZ4KimAmsterdamXAY4
AXZ5RameshNew DelhiXAY5
AXZ6HuangBeijingXAY6

❮ T-SQL Functions