Home » SQL SERVER REPLACE()

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’




Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.