EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials SQL Tutorial SQL Server Replace
 

SQL Server Replace

Roja Metla
Article byRoja Metla
EDUCBA
Reviewed byRavi Rathore

Updated July 6, 2023

SQL Server Replace

 

 

Introduction to SQL Server Replace

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

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

1) Actual_string

2) Old_substring

3) New_substring.

Here Actual_string resembles the string in 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 operations in the database column as well. We need to specify the required column that needs 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 the 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:

SQL Server Replace 1

Now let us replace the column with some particular string. The thing to remember is that 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:

SQL Server Replace 2

Here in the above table, we have inserted animal names that have an ‘a’ string in them. But we have misplaced the ‘a’ with ‘s.’

Eg:- Cat ->cst

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

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

Output:

SQL Server Replace 3

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 if the data is updated. Updated data output should be in the below manner: –

Output:

SQL Server Replace 4

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 the same. Let us see an example of the same.

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

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

Output:

SQL Server Replace 5

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:

Output 6

Example #2

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

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:

Output 7

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

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

Output:

Output 8

To update the change in the table:

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

Output:

output

Recommended Articles

We hope that this EDUCBA information on “SQL Server Replace” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

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

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW