SQLite REPLACE Function

Summary: in this tutorial, you will learn how to use SQLite REPLACE() function to replace all occurrences of a specified string with another string.

Introduction to SQLite REPLACE() function

The SQLite REPLACE() function is a string function that allows you to replace all occurrences of a specified string with another string.

The following shows the syntax of the REPLACE() function:

REPLACE(string,pattern,replacement)

In this syntax:

• string is the string that you want to perform the replacement.
• pattern is the substring to be found in the original string
• replacement is the replacement string.

The REPLACE() function is useful for updating character data in a table e.g., update the dead links and typos.

SQLite REPLACE() function examples

Let’s take some examples of using the REPLACE() function.

SQLite REPLACE() function simple example

The following example replaces the string 'A' in the string 'AA B CC AAA' with the new string 'Z':

SELECT
    REPLACE('AA B CC AAA','A','Z');

The following picture shows the output:

The following statement uses the REPLACE() function to replace the string 'This' in the string 'This is a cat' with the new string 'That':

SELECT
    REPLACE('This is a cat','This','That');

The output is:

SQLite REPLACE() string in a table example

We will use the REPLACE() function to replace strings in a table.

First, create a new table named contacts that consists of four columns: contact_id, first_name, last_name, and phone:

CREATE TABLE contacts (
    contact_id INT PRIMARY KEY,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    phone TEXT NOT NULL
);

Next, insert into the contacts table some rows:

INSERT INTO contacts(first_name, last_name, phone)
VALUES('John','Doe','410-555-0168');
INSERT INTO contacts(first_name, last_name, phone)
VALUES('Lily','Bush','410-444-9862');

Then, query data from the contacts table:

SELECT 
    first_name, 
    last_name,
    phone
FROM 
    contacts;

Here are the contents of the contacts table:

After that, update the phone area code from local (410) to international one(+1-410):

UPDATE
    contacts
SET
    phone = REPLACE(phone,'410','+1-410');

Finally, query data from the contacts table to verify if the phone has been updated:

SELECT 
    first_name, 
    last_name,
    phone
FROM 
    contacts;

As shown clearly in the output, the phone numbers have been updated successfully.

In this tutorial, you have learned how to use the SQLite REPLACE() function to replace all occurrences of a substring in a string with a new string.