Replace in SQL: How to Use Replace() function

In this detailed blog, we will understand the replace function in SQL. From the syntax and examples to how to use REPLACE() in SQL, we will go into each of its functionalities one by one by implementing practical examples. By the end of this blog, we will have an idea of what the Replace function in SQL is.

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?

This REPLACE function in SQL is powerful for manipulating strings to locate a certain substring in the string that will be searched to replace another substring. In general, it can prove useful when cleaning and updating textual data in a database. In particular, it operates case-insensitively, replacing all occurrences regardless of case.

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

The SQL REPLACE function is used to replace the string inside datasets. This simplifies data cleaning and updating because one can replace specified substrings efficiently. Understanding the syntax of this function and applying it through real-life examples would be able to empower the users for better database management skills.