Using REPLACE Function in SQL

In Structured Query Language (SQL), the REPLACE function is used to replace a substring or a part of a string within the given String. While dealing with some data pre-processing tasks or some data cleaning tasks, the REPLACE function is found to be very useful. It can save a lot of time and it also performs the tasks with great precision. There may be some cases where we need to replace some redundant data with some filler values, in those types of cases REPLACE function can be used.

In this article, we are going to discuss some applications of REPLACE function. We will also discuss some examples related to REPLACE functions with their respective explanations.

REPLACE Function in SQL

In SQL, the REPLACE function is used to modify a string or replace a substring within a string. The REPLACE function is not a standalone function. We commonly use the REPLACE function with the UPDATE statement or with the SELECT Statement. We commonly use the REPLACE function in data manipulation tasks.

Syntax:

REPLCACE ( String, S1, S2 )

Where S1 is the substring to be replaced and S2 is the replacement substring.

Setting up the Table

In this article, we will use the geeksforgeeks table for example.

Table: geeksforgeeks

Table – geeksforgeeks

To Create Table:

CREATE TABLE geeksforgeeks(
  id int PRIMARY KEY,
  name varchar(100),
  course varchar(100)
 );

To insert values in the table and display them:

--inserting data to the table
INSERT INTO geeksforgeeks (id, name , course)
VALUES
    (1, 'Vishu', 'Python - DSA Self Paced'),
    (2, 'Sumit', 'Java Self Paced'),
    (3, 'Neeraj', 'Java Self Paced'),
    (4, 'Aayush', 'JavaScript Self Paced'),
    (5, 'Vivek', 'Python with SQL Self Paced'); 

--displaying table's data 
SELECT* FROM geeksforgeeks;

Example of REPLACE Function in SQL

In this, we are going to discuss various examples related to the REPLACE function. We will implement REPLACE function with UPDATE and SELECT statements.

Example 1: REPLACE function with Update statement.

In this example, we will implement REPLACE function with the UPDATE statement. We will also consider two cases i.e. one without the WHERE Clause and one with the WHERE Clause.

CASE 1: With WHERE Clause

Given the table ‘geeksforgeeks’, we will update all entries in the ‘course’ column where the course is currently having ‘Python’ to instead be listed as ‘C++’.

Query:

UPDATE geeksforgeeks
SET course = REPLACE(course, 'Python', 'C++')
WHERE course LIKE '%Python%';

Output:

Replace function with where clause

Explanation: In the above table, we can see that id’s 1, 5 have previously got Python in the course column but now it has C++ in the course column. We have also used LIKE operator, to get all the records that contains ‘Python’ anywhere in the course column.

CASE 2: Without WHERE Clause

We will perform similar kinds of operations as we have done in case 1 but more simple. We will implement REPLACE function with the UPDATE statement without where clause. We will update courses from ‘Self Paced‘ to ‘Classroom‘.

Query:

UPDATE geeksforgeeks
SET course = REPLACE(course, 'Self Paced', 'Classroom');

Output:

Replace function without where clause

Explanation: In the above image, we can see that all the Self-paced words from course column are now have been replaced with Classroom words. By seeing the query, we can conclude that REPLACE function can work without WHERE Clause too.

Example 2: REPLACE function with SELECT statement.

In this example, we will see an implementation of how to use REPLACE function with a SELECT statement.

Query:

SELECT REPLACE('Python Self Paced', 'Python', 'Java') as Result;

Output:

Replace function with SELECT statement

Explanation: In the above query, ‘We have specified a string ‘Python Self Paced’. Now we have used REPLACE function to replace the ‘Python‘ word with ‘Java’. In the image, we can observe that in place of ‘Python‘, ‘Java‘ is displayed in the string.

Conclusion

Overall, the REPLACE function is used in the manipulation of a string. It is used to replace or modify a substring within the given string. It is useful in many data manipulation related tasks such as data cleaning. We can remove redundant data with the help of the REPLACE function. We have seen how we can use REPLACE function with the UPDATE statement as well as the SELECT statement. Now you have a good knowledge of the REPLACE function. Now you can write queries related to it and can the desired output.