SQL SERVER REPLACE

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.

SYNTAX

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’.

Also Read..

REPLICATE()

UPPER()

LOWER()

TRIM()

LRTIM()

RTIM()

SPACE()

TRANSLATE()