Replace Function in SQL Server

Posted April 7, 2015 by Vishwanath Dalvi in Database, SQL Server

feature-sql1

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

sql server replace function example 4-5 query

sql server replace function example 4-5 results

 

About Vishwanath Dalvi

Vishwanath Dalvi is a gifted engineer and tech enthusiast. He enjoys music, magic, movies, and gaming. When not hacking around or supporting the open source community, he is trying to overcome his phobia of dogs.
View more articles by Vishwanath Dalvi

The Conversation

Follow the reactions below and share your own thoughts.