Replace in SQL: Usage and Implementation of REPLACE() Function

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

• What is Replace in SQL?
• Syntax and Example of Replace in SQL
• How to Use Replace in SQL with Update Query
• Conclusion
• FAQs

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:

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:

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:

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 Basic 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:

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:

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:

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.

FAQs