SQL Server Replace

Introduction to SQL Server Replace

Replace function is used to replace all the occurrence of the substring that you have specified. Normally we specify three arguments in the Replace function.

1) Actual_string

2) Old_substring

3) New_substring.

Here Actual_string resembles the string which you want to make the changes take place. Old_substring is the substring of Actual_string which you want to be changed to the New_substring.

We can perform replace operation in the database column as well. We need to specify the required column that need to replace with the New_substring.

In this session let us learn a few things like How and Where the “Replace function” is used.

Syntax:

Now let us see the syntax for the replace function:

replace(Actual_string, Old_substring, New_substring)

Now let us see the syntax for the replace function if we want to perform the operation on the column of database table:

replace(column_name, Old_substring, New_substring)

How to use Replace in SQL Server?

Now let us see how the replace function works in the SQL server:

select replace('Sunction','S','F')as "Replace function";

Output:

Now let us replace the column with some particular string. The thing to remember if u specify a replace function in a column whole column will be replaced with the mentioned string.

Let us create a table with default data in it and perform the replace function in one column.

Table creation:

create table replace_test
(
Serial_no int,
Animal_name varchar(20)
)

Insert data into the Table:

insert into replace_test values ( 1,'cst');
insert into replace_test values ( 2,'besr');
insert into replace_test values ( 3,'Elephsnt');
insert into replace_test values ( 4,'rsbbit');
insert into replace_test values ( 5,'girsffe');
insert into replace_test values ( 6,'msmmsl');
insert into replace_test values ( 7,'gost');
insert into replace_test values ( 8,'bst');
insert into replace_test values ( 9,'esgle');
insert into replace_test values ( 10,'gorills');
select * from replace_test;

Output:

Here in the above table, we have inserted animal names which has ‘a’ string in it. But we have misplaced the ‘a’ with ‘s’.

Eg:- Cat ->cst

Bear ->besr and so on. Now let us replace the ‘s’ substring to ‘a’ from the column “animal_name”.

select *, Replace(animal_name,'s','a') as "Reformatted animal name" from replace_test;

Output:

From the above output, we could observe that the ‘s’ is replaced with the ‘a’.

To update the column values we can do the below:

update replace_test
set Animal_name = replace( animal_name,'s','a');
select * from replace_test;

Now we have updated the animal replace column with the required replacement. Let us see is the data is updated. Updated data output should be in the below manner: –

Output:

We could see that the data has been updated in the table as well.

Replace function is not a case- sensitive. Hence ‘s’ and ‘S’ are same. Let us see an example for the same.

select replace('Sassal','S','m')as "Replace function";

Here above example is to replace the ‘s’ to ‘m’. As replace is not case sensitive all the ‘s’ string will be replace with ‘m’.

Output:

Example

Now let us consider a few examples for the replace function and its output as below: –

Example #1

select replace('repldce','d','a')as "Replace function";

Output:

Example #2

Now let us consider the table of students along with their phone number.

Table Creation:

create table replace_test_student
(
stud_name varchar(20),
stud_phoneno varchar(20)
);

Insert data into the table:
insert into replace_test_student values ('Ram','(901)2345672');
insert into replace_test_student values ('Sai','(900)3445672');
insert into replace_test_student values ('Rahul','(991)7045672');
insert into replace_test_student values ('Sham','(951)2345672');
insert into replace_test_student values ('Subhu','(971)2345672');
insert into replace_test_student values ('Srusthi','(912)2345655');
insert into replace_test_student values ('Navya','(912)6745621');
insert into replace_test_student values ('Priya','(923)2345574');
insert into replace_test_student values ('William','(991)0945871');
insert into replace_test_student values ('Ben','(891)3045672');
select * from replace_test_student;

Output:

Now let us replace the ‘(‘ and ‘)’ with none.

select *,replace(replace(stud_phoneno,'(',''),')','') as "formatted student phoneno" from replace_test_student;

Output:

To update the change into the table:

update replace_test_student
set stud_phoneno = replace(replace(stud_phoneno,'(',''),')','');
select * from replace_test_student;

Output:

This is a guide to SQL Server Replace. Here we discuss introduction, syntax, How to use Replace in SQL Server? and example with code implementation. You may also have a look at the following articles to learn more –

1. SQL NULLIF()
2. SQL ROW_NUMBER
3. SQL NTILE()
4. MySQL ROUND