While managing an extensive database as an data analyst, you might sometimes want to replace a string data field substring with another. One option you have is to delete the record and then add it with a new value. That’s the basic way that pops into everyone’s mind. But ‘replace in SQL’ provides another efficient way to do this.
What is Replace in SQL?
Replace in SQL is a built-in function that allows you to replace all the incidents of a substring within a specified string with a new substring. Thus, whenever you want to replace something like a dead link or a product name, the replace() function is the way to go. The basic syntax of replace in SQL is:
REPLACE(String, Old_substring, New_substring);
In the syntax above:
- String: It is the expression or the string on which you want the replace() function to operate.
- Old_substring: It is the substring that you want to look for in the string and replace.
- New_substring: It is the new substring that you want to include in place of the Old_substring.
Note: The strings that you include in the replace() function are case-sensitive. Thus, always make sure to use the exact string that you want to search for and replace.
Let’s look at an example to better understand the syntax of replace in SQL and how it works.
SELECT
REPLACE('This is Java Tutorial', 'Java', 'SQL');
Output:
As you can see in the output, the substring ‘Java’ was replaced by ‘SQL.’ Thus, the replace function will search for Java and replace it with SQL. This was a simple use case with only a single occurrence of the Old_substring. Let’s look at another example where there are multiple occurrences of the Old_substring and replace them with the New_substring.
SELECT
REPLACE('This tea store serves the best tea from a teapot', 'tea', 'coffee');
Output:
As you can see, all the occurrences of the substring ‘tea’ were replaced with the New_substring ‘coffee,’ even if the substring was attached to other characters as with the word ‘teapot.’ But if you pass an Old_substring that is not present in the string expression, the SQL replace() function will return the string as it is. Let’s look at an example for that too.
SELECT
REPLACE("Welcome to Simplilearn's SQL Tutorials", 'Sql', 'Java');
Output:
Since the replace() function search is case sensitive, it will not consider ‘Sql’ the same as ‘SQL.’ Thus, for the server, there is no SQL in the original string. Hence, it will simply return the exact string without making any changes.
How to Use Replace in SQL With the UPDATE Statement?
In this article, you have seen some examples of using replace in SQL with literal strings. However, in SQL, you will work with tables and databases. So let’s move to use the SQL replace() function along with the UPDATE statement to update data in a table.
A real-world example can be managing the Customers' table of global clients. Suppose you have entered the wrong country code in the contact list and want to replace it with the correct code for all the country’s contacts. That’s where you can use replace in SQL. Before getting into anything, create the Customers table using the CREATE TABLE command and insert some values using the INSERT INTO command.
-- Creating the table
CREATE TABLE Customers(
C_ID int PRIMARY KEY,
C_FirstName VARCHAR(20) NOT NULL,
C_LastName VARCHAR(20) NOT NULL,
Country VARCHAR(20) NOT NULL,
Contact_No NVARCHAR(15) UNIQUE,
Email_ID NVARCHAR(35)
);
-- Inserting some values
INSERT INTO Customers VALUES (1, 'Aakash', 'Roy', 'India', '+91 861.543.1240', 'ar.com');
INSERT INTO Customers VALUES (2, 'Adam', 'Frip', 'USA', '+2 651.283.1942', 'af.com');
INSERT INTO Customers VALUES (3, 'David', 'Hemsworth', 'France', '+33 527.535.4569', 'dh.com');
INSERT INTO Customers VALUES (4, 'Rahul', 'Chopra', 'India', '+91 935.283.9212', 'rc.com');
INSERT INTO Customers VALUES (5, 'Chris', 'Martin', 'USA', '+2 726.835.9625', 'dm.com');
INSERT INTO Customers VALUES (6, 'Charles', 'Johnson', 'France', '+33 650.239.5647', 'cj.com');
INSERT INTO Customers VALUES (7, 'George', 'Austin', 'India', '+91 932.751.6583', 'ga.com');
-- Fetching the results
Select * FROM Customers;
Output:
If you would have noticed, this demo incorrectly entered the country code for the USA clients to be ‘+2’ instead of ‘+1.’ Let’s use replace in SQL along with the UPDATE statement to correct this error.
UPDATE Customers
SET
Contact_No = REPLACE(Contact_No, '+2', '+1');
-- Fetching the outcome
SELECT * FROM Customers;
Output:
As you can see, all the Contact_No data with ‘+2’ were replaced with ‘+1’. Thus, the mistakes in all the fields were quickly taken care of with a single line of command with the use of replace in SQL. However, this was a small table. But while working with large tables where the number of affected rows is more, it is essential to check if the function executes as expected. You can check it with the WHERE clause’s help to first apply it in a single row. Now, use the WHERE clause to replace the ‘.’ character in the Contact_No column with ‘-’ for a row with C_ID = 5 before applying it to the entire table.
UPDATE Customers
SET
Contact_No = REPLACE(Contact_No, '.', '-')
WHERE
C_ID = 5;
-- Fetching the outcome
SELECT * FROM Customers
WHERE C_ID = 5;
Output:
Now that you know it works as expected, you can apply it to the entire table by removing the WHERE clause.
UPDATE Customers
SET
Contact_No = REPLACE(Contact_No, '.', '-');
-- Fetching outcome
Select * FROM Customers;
Output:
As you can see, all the ‘.’ characters in the Contact_No field were replaced with the ‘-’ character. One more thing worth noting is that you have passed Contact_No as a column and not as a string expression, which is a simple mistake to make. Thus, don’t use single inverted commas (‘’) while passing the first argument in the SQL replace() function. Let’s see what happens if you pass the first expression as a column. In the example below, you will try changing the substring ‘com’ from Email_ID to ‘simplilearn.org.’ First, you have to pass Email_ID as a literal string and later as a column and see the results.
UPDATE Customers
SET
Email_ID = REPLACE('Email_ID', 'com', 'simplilearn.org');
SELECT * FROM Customers;
Output:
As you can see in the output, instead of changing the substring, the replace() function changed the entire column’s field to ‘Email_ID’ as you had passed it as a literal string and not a column. If instead of using it as a string expression, you remove the inverted commas and give it as a column. Then it will work as expected. Let’s look at the output for that.
UPDATE Customers
SET
Email_ID = REPLACE(Email_ID, 'com', 'simplilearn.org');
Select * FROM Customers;
Output:
As expected, this time, the SQL replace() function changed the ‘org’ substring in the Email_ID column from the Customers table to ‘simplilearn.org.’
Conclusion:
In this article, you have learned everything you need to know about replace in SQL. The SQL replace() function comes in handy to easily and quickly change any particular data from vast and enormous databases and tables. Read our next tutorial on NVL function in SQL.
Using replace in SQL is a fundamental concept that is essential to manage databases efficiently. However, besides such basic concepts, it is also essential to learn about and use all the advanced SQL concepts too. If you want to know all those advanced concepts, you can refer to Simplilearn’s Data Analyst Master's Program. The training is adept at helping you excel in writing SQL queries.
Have any questions for us regarding “replace in SQL”? Leave them in the comments section, and our experts will get back to you on the same, ASAP.