SQL SERVER REPLACE function is used to replace all occurrences of a substring in a string with a new substring.
It searches for substring text and replace it with new substring text in string.
REPLACE(input_string, old_substring, new_substring)
Lest look at an example of REPLACE function in SQL.
Replacing a character within string using REPLACE Function
Following statement, uses REPLACE function and replaces the character ‘A’ with ‘D’ within string ‘ABC’.
DECLARE @txt VARCHAR(3) SET @txt = 'ABC' SELECT @txt AS Txt, REPLACE(@txt, 'A', 'D' ) AS [ReplacedTxt]
As you can see, it replaces the character A with D within string ‘ABC’.
Replacing a Substring within string using REPLACE function
Following statement, uses REPLACE function and replace the substring Coffee with Tea with in string ‘Soft Drink, Coffee, Tea, Beer’.
DECLARE @txt VARCHAR(50) SET @txt = 'Soft Drink, Coffee, Tea, Beer' SELECT @txt AS Txt, REPLACE(@txt, 'Coffee', 'Tea' ) AS [ReplacedTxt]
As you can see, It replaces the substring text ‘Coffee’ with new substring text ‘Tea’ within the given string.
Replacing a substring in column using REPLACE Function
To demonstrate this, Lets create a table named EmpDpt and insert some records into table.
CREATE TABLE dbo.EmpDpt (Id INT, Name VARCHAR(50), Department VARCHAR(50) ) INSERT INTO dbo.EmpDpt (Id, Name, Department) VALUES (1, 'Jack Hanery', 'IT'), (2, 'Joshep Mac', 'HR'), (3, 'Rozer Jr', 'IT'), (4, 'Mustafha Md.', 'Finance')
Now we have a table EmpDpt as shown below.
SELECT * FROM dbo.EmpDpt
Lets replace the string ‘IT’ with ‘Information Tecnology’ in column Department.
As you can see, it replaces the string ‘IT’ with ‘Information Technology’.