Replace in SQL: How to Use Replace() function
Updated on 03rd Jan, 24 9.1K Views

In this comprehensive blog, we will understand the Replace function in SQL. From Syntax and examples to the use of REPLACE() in SQL, we will explore each of its functionalities in detail by implementing real-life examples. By the end of this blog, we will have a clear understanding of Replace in SQL.

Table of Contents

Watch this video to learn SQL in detail

What is Replace in SQL?

The replace function in SQL is a powerful tool for modifying strings within datasets. It allows users to search for a specific substring within a string and replace it with another. This function operates on text data and is particularly useful when dealing with textual data within the table. The strings in the Replace function do not follow case sensitivity,  so it will update every substring, be it in lowercase, uppercase, or both.

Enroll in this professional SQL Certification Course to learn more about databases and SQL from experts.

Syntax and Example of Replace in SQL

Replace in SQL works with a basic syntax to replace strings in the dataset, as mentioned below.

Syntax:

REPLACE(String, Old_substring, New_substring);

  • String: String represents the expression or the string on which you want to implement the REPLACE() function.
  • Old_substring: It is the substring that you want to look for in the string and replace.
  • New_substring: You want to update the new substring in place of the Old_substring.

Examples of REPLACE() in SQL: Replacing a single string

Query:

SELECT

REPLACE ('XYZ offers best certification courses from industry experts’ , 'XYZ', 'Intellipaat') as Updated_text

Output:

Example of Replace in SQL

In the above example, we have replaced XYZ with Intellipaat using the replace function and given the column alias as Updated_text.

Replacing Multiple Strings:

SELECT 

REPLACE ('XYZ welcomes you.!, Get the best courses from XYZ and excel in your career,' , 'XYZ', 'Intellipaat') as Updated_text

Output:

Replacing Multiple Strings

In the above example, all the substrings with XYZ have been replaced by Intellipaat using the REPLACE function in SQL.

Case Sensitivity of Using the Replace Function: As we have already mentioned, the Replace function does not check for case sensitivity. It can match any same substring.

Let us understand it with the help of an example:

Query:

SELECT 

replace ('xYz welcomes you to this page, 'XYZ', 'INTELLIPAAT') as Updated_text

Output:

Case Sensitivity of Replace in SQL

Disclaimer: SQLite checks for the case sensitivity and gives the output for substrings with exact match only. 

Prepare yourself for the industry by going through these SQL Interview Questions now!

How to Use Replace in SQL with Update Query

To show the use of the Replace function in SQL with an update query, we will create an Employee table with Emp_ID, Emp_FirstName, Emp_LastName, Designation, and Email_ID. Suppose the email IDs are updated in the wrong format, so we will replace them and update them correctly as per the required format.

Creating a table:

CREATE TABLE Employee(

    Emp_ID int PRIMARY KEY,

    Emp_FirstName VARCHAR(20),

    Emp_LastName VARCHAR(20) ,

    Designation VARCHAR(20),

    Email_ID NVARCHAR(35)

);

INSERT INTO Employee VALUES (1, 'Ram', 'Prasad', 'Manager', 'ram.rp.com');

INSERT INTO Employee VALUES (2, 'Saiyyad', 'Sheikh', 'Intern', 'saiyyad.ss.com');

INSERT INTO Employee VALUES (3, 'Himanshu', 'Singh', 'HOD', ‘himanshu.hs.com');

INSERT INTO Employee VALUES (4, 'Rahul', 'Mehta', 'Executive', 'rm.com');

INSERT INTO Employee VALUES (5, 'Reshma', 'Srivastav', 'Sales Engineer', 'rs.com');

Execution:

Select * FROM Employee;

Output:

How to use Replace in SQL with Update query

Now, we will update the Email_ID of the employee in a particular format using the update query as shown below:

Query:

UPDATE Employee

SET

Email_ID = REPLACE(Email_ID, '.com', '@gmail.com');

SELECT * FROM Employee;

Output:

Select * from Employee

We can also use an update statement with the WHERE clause, as shown below:

Query:

UPDATE Employee

SET

Emp_LastName = 

replace (Emp_LastName , 'Singh' , 'Sharma') as Updated_text

WHERE

Emp_ID = 3;

Execution:

Select * from Employee

Output:

Using Update Query with Replace in SQL

Conclusion

In this blog, we have understood the concept of replace in SQL, its functionalities, and the implementation of this function using a real-life example of employee details and performed various operations on it. We used the UPDATE statement with the WHERE clause to update the replaced values in the dataset.

Drop any of your queries in our SQL Server Community and start a discussion.

FAQs

Is the REPLACE function in SQL case-sensitive?

The REPLACE function in SQL is not case-sensitive. It replaces all instances of the specified string, irrespective of their case. For example, replacing ‘abc’ in a string will replace ‘abc’, ‘AbC’, ‘aBC’, etc.

Can I use REPLACE to update data in multiple columns simultaneously?

No, the REPLACE function operates on individual columns or strings. It does not allow simultaneous updates across multiple columns in a single command. To update multiple columns, separate UPDATE statements are required, each targeting a specific column.

Does REPLACE modify the original data or create a new set of data?

REPLACE modifies the original data in the specified column. It doesn’t create a new set of data or alter the structure of the table. It simply updates the values within the column based on the defined replacement pattern.

What happens if the string to replace doesn't exist in the original text?

If the string to be replaced is not found within the original text, the REPLACE function will not make any changes to the text. It will leave the original string intact, and the output will be identical to the input.

Is it possible to perform a wildcard-based replacement using REPLACE?

No, the REPLACE function in SQL does not support wildcard characters like ‘%’ or ‘_’. It only replaces exact matches of the specified string within the given text or column. To perform wildcard-based replacements, other functions or methods need to be used, like using the `LIKE` operator in combination with `UPDATE`.

Course Schedule

Name Date Details
SQL Training 02 Mar 2024(Sat-Sun) Weekend Batch
View Details
SQL Training 09 Mar 2024(Sat-Sun) Weekend Batch
View Details
SQL Training 16 Mar 2024(Sat-Sun) Weekend Batch
View Details

Leave a Reply

Your email address will not be published. Required fields are marked *

Speak to our course Advisor Now !

Related Articles

Associated Courses

Subscribe to our newsletter

Signup for our weekly newsletter to get the latest news, updates and amazing offers delivered directly in your inbox.