How to UPDATE and REPLACE Part of a String in SQLite

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.

How to UPDATE and REPLACE Part of a String?

When working with SQLite, there are several approaches to update and replace parts of a string. We can achieve this using SQLite’s built-in string functions or through queries that utilize the REPLACE and UPDATE statements. Below are the methods that help to update and replace strings are as follows

1. Using REPLACE function
2. Using SUBSTR function
3. Using INSTR function

To understand How to UPDATE and REPLACE part of a string in SQLite we need a table on which we will perform various operations and queries. Here we will consider a table called geeksforgeeks which contains id, name, courses, and potd_streak as Columns. After inserting some records into the table, the table looks

Table – geeksforgeeks

1. Using the REPLACE Function

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 geeksforgeeks
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 geeksforgeeks
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.

2. Using SUBSTR Function

The SUBSTR function is used to extract a substring from the given string.

Syntax:

SUBSTR( String , Starting Position , Desired Length)

In this example, we are going to explore SUBSTR function. We are going to cover is basic syntax with well structured example.

Query:

UPDATE geeksforgeeks
SET courses = SUBSTR(courses, 1, 4)
WHERE courses LIKE 'DSA%' ;

Output:

Output

Explanation : In this example, we have used SUBSTR function to extract a part of the string. We have specified changes in courses column where string starts with DSA. We have extract DSA from the string and set it to the column’s value.

3. Using INSTR Function

The INSTR function is used to find the first occurrence of a specified substring string within a given string.

Syntax:

INSTR ( String , Substring to be searched )

In this example, we are going to explore INSTR function. We are going to explore its basic syntax and how to use it form a well structured query.

Query

SELECT id, name, courses, INSTR(courses, 'self paced') AS answer 
FROM geeksforgeeks;

Output:

Using tNSTR function

Explanation : In the above image, we can observe that all the records which have ‘self paced’ in there courses column have a corresponding value in there answers column. Each specify the starting index of the ‘self paced’ in the string.

Conclusion

Overall, updating and replacing parts of a string can be efficiently achieved using the REPLACE function for updating specific parts of a string, the SUBSTR function for extracting substrings, and the INSTR function for finding substring positions. These functions, combined with SQL queries, offer powerful tools for managing and manipulating string data in SQLite databases.