How to use the REPLACE Function In SQL

The REPLACE function is used to replace or modify any specific values. REPLACE function is not a stand-alone statement. We generally use the REPLACE function with UPDATE or SELECT statement.

Syntax:

REPLCACE ( String, R1, R2 )

Explanation: Where R1 is the substring string to be replaced and R2 is the replacement substring.

Example 1: Without WHERE Clause

In this example, we are going to update a single column values and replace a particular part of string from each column values. We are going to perform this operation on our ‘courses’ column.

Query :

UPDATE w3wiki
SET courses = REPLACE(courses, 'Live', 'self paced');

Output:

Explanation : We can clearly notice the difference in the courses column values. All the values where “Live” word is present previously, those values have been replaced by “self paced”. In the above image, we can clearly observe the difference.

Example 2: WHERE Clause with Multiple Condition

In this example, we are going to change some specific values from a particular column. But unlike in the previous example, we are going to have multiple condition instead of single condition.

Query:

UPDATE w3wiki
SET courses = REPLACE(courses, 'Python', 'Java')
WHERE courses LIKE 'Python%' and id in (1,5);

Output:

Explanation : In this example, we have specified multiple conditions. We have specified changes only on those records from courses column which satisfy both the conditions i.e. String should start with “Python” and those records which has id’s in the list (1,5). If any records satisfy both of these conditions then, we change those record’s values from having ‘Python’ as staring of the sentence to have ‘Java’ as starting of the sentence. We can clearly observe the changes for rows with id’s 1 and 5.

In SQLite, updating and replacing parts of a string can be a common task, especially when dealing with textual data. SQLite, serverless architecture offers various methods to solve this problem. In this article, We will learn about string replace in a query with the help of various methods to know how to update and replace a part of the string in SQLite and so on.