Tuesday, July 30, 2013

Replace Function in SQL Server

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