SQLite Replace Statement

SQLite is a database engine. It is a serverless architecture as it does not require any server to process queries. Since it is serverless, it is lightweight and preferable for small datasets. It is used to develop embedded software. It is cross-platform and available for various Operating systems such as Linux, macOS, Windows, Android, and so on.

Replace Statement

REPLACE Statement is a type of Statement that helps us to replace one part of a string with another string. It is an INSERT OR REPLACE command which means if the record already exists in the table then it replaces the substring with a new string, but if the record does not exist in the table then INSERT new record into the table. The REPLACE Statement is similar to the INSERT statement. We will understand everything with the help of examples.

Syntax:

SELECT REPLACE(A,B,C)

• A -> Original String present in the Table.
• B -> Substring or string that we need to replace.
• C -> Replace string.

Examples 1:

Let’s say we have an ‘ABC DEF ANM ADK DLM‘ string and we have to replace substring ‘A‘ with ‘S‘ String. here we use REPLACE Statement.

SELECT  REPLACE('ABC DEF ANM ADK DLM', 'A', 'S')

Output:

SBC DEF SNM SDK DLM

Explanation: In the output you can clearly see that all substring where earlier ‘A‘ lies but after REPLACE statement it change with ‘S‘ string.

Example 2:

If the second string does not exist then it replace nothing and returns the original string.

SELECT REPLACE('ABC DEF ANM ADK DLM', ' LA', 'S')

Output:

ABC DEF ANM ADK DLM

Explanation: In the above query we are trying to replace all substring ‘LA‘ with ‘S‘. Their is no subtsring with ‘LA‘, hence, it returns the original string.

Example 3:

What if we are trying to find substring which is empty string and we want to replace it with another string. Does REPLACE work with empty string ? Let’s understand .

SELECT REPLACE('ABC DEF ANM ADK DLM', '  ', 'S')

Output:

ABC DEF ANM ADK DLM

Explanation: In the above query we are trying to replace string with the empty substring. But REPLACE statement is not work with empty string so it return Original string and nothing will change.

Example with Table

Let’s say we have a Employee table and a coulumn named employee name. Now we want to replace ‘BC’ with ‘##’ evrytime when BC appears in empname coloumn. To do this we can use the REPLACE statement.

Employee Table

SELECT empid,empname,city,REPLACE(empname, 'BC', '##') newName FROM Employee

Output:

After REPLACE Operation

Explanation: In the above query, we have replaced all empname whose name contains ‘BC‘ as substring with ‘##‘. After performing the REPLACE statement our new employee table will look the above table.