Replace Function in SQL Server

2480

A replace function in SQL Server is used to replace single or multiple characters with another single or multiple-character string.

Syntax: Replace Function in SQL Server

REPLACE (input_string_expression, string_pattern, string_replacement)

input_string_expression: input string of characters where string_pattern will be searched
string_pattern: string to be searched within an input string expression
string_replacement: sting to be replaced in an input string

Let’s start with a few examples.

1. Replace a single character in a string:

SELECT REPLACE('bing','b','p') as Example1;

In the example above, character b in bing is replaced with p and returns a new string ‘ping.’

2. Replace multiple occurrences of a character in a string:

SELECT REPLACE('Vishwanath','a','v') as Example2;
SELECT REPLACE('Vishal','i','v') as Example2;

In the example above, the multiple occurrences of a single character is replaced by the replacement character and returns a new string.

3. Replace a string with another:

SELECT REPLACE('BMW is my favorite car', 'BMW', 'Jaguar') as Example3;

In the example above, string BMW is replaced with Jaguar, and a new string is returned as output.

sql server replace function example 1-3

4. Replace function on table column:

CREATE TABLE #Employee
(
EmployeeId INT,
EmpName VARCHAR(50)
);

INSERT INTO #Employee VALUES (1, 'Atul_Kokam');
INSERT INTO #Employee VALUES (1, 'Sang_W');
GO

--Replacing _ (Underscore) in employee name with a space.

SELECT REPLACE(EmpName, '_', ' ') As CorrectedEmpName
FROM   #Employee;

5. Replace Function with Update statement in SQL Server:

In the example above, we replaced _ (Underscore) in employee name with a space. That change was only made while displaying the employee name. We can use a replace function to make permanent changes in our table.

CREATE TABLE #Employee
(
EmployeeId INT,
EmpName VARCHAR(50)
);

INSERT INTO #Employee VALUES (1, 'Atul_Kokam');
INSERT INTO #Employee VALUES (1, 'Sang_W');
GO

SELECT * FROM #Employee; --With Underscore

--Replacing _ (Underscore) in employee name with a space.

UPDATE #Employee
SET    EmpName = REPLACE(EmpName, '_',' ');

SELECT * FROM #Employee; --Replaced Underscore with Space