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 ‘w3wiki’, we will update all entries in the ‘course’ column where the course is currently having ‘Python’ to instead be listed as ‘C++’.
Query:
UPDATE w3wiki
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 w3wiki
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.
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.