The SQLite replace() function enables us to replace one string (or part of a string) with another string.

The way it works is that you provide three arguments; the string that contains the substring to replace, the substring within that string to replace, and the string to replace it with.

Syntax

The syntax goes like this:

replace(X,Y,Z)

The function returns a string formed by substituting string Z for every occurrence of string Y in string X.

The BINARY collating sequence is used for comparisons. If Y is an empty string then return X unchanged. If Z is not initially a string, it is cast to a UTF-8 string prior to processing.

Example

Here’s a simple example to demonstrate.

SELECT replace('Red Sky', 'Red', 'Blue');

Result:

Blue Sky

If the second argument is not found in the first argument, then nothing is replaced and the original string is returned.

SELECT replace('Red Sky', 'Green', 'Blue');

Result:

Red Sky

If the second argument is an empty string, then again, nothing is replaced and the original string is returned.

SELECT replace('Red Sky', '', 'Blue');

Result:

Red Sky

But the same can’t be said about the third argument. If that’s an empty string (and the second argument is found in the first argument) then it will remove the second argument from the string.

SELECT replace('Red Sky', 'Red', '');

Result:

 Sky

However, this won’t happen if the second argument is not found in the string.

SELECT replace('Red Sky', 'Blue', '');

Result:

Red Sky

Replacing with a Non-String

If the third argument is not a string, it’s cast to a UTF-8 string prior to processing.

SELECT replace('Red Sky', 'Red', 8);

Result:

8 Sky

Database Example

Here’s an example that takes data from a database table and replaces a given string/substring with another string.

SELECT 
  ProductName,
  replace(ProductName, 'Widget', 'Car')
FROM Products;

Result:

ProductName     replace(ProductName, 'Widget', 'Car')
--------------  -------------------------------------
Homer's Widget  Homer's Car                          
Peter's Widget  Peter's Car                          
Bob's Widget    Bob's Car