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)
Code language: SQL (Structured Query Language) (sql)

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');
Code language: SQL (Structured Query Language) (sql)

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');
Code language: SQL (Structured Query Language) (sql)

The output is:

SQLite REPLACE Function simple example

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
);
Code language: SQL (Structured Query Language) (sql)

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');Code language: SQL (Structured Query Language) (sql)

Then, query data from the contacts table:

SELECT 
    first_name, 
    last_name,
    phone
FROM 
    contacts;Code language: SQL (Structured Query Language) (sql)

Here are the contents of the contacts table:

SQLite REPLACE Function sample 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');Code language: SQL (Structured Query Language) (sql)

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

SELECT 
    first_name, 
    last_name,
    phone
FROM 
    contacts;Code language: SQL (Structured Query Language) (sql)
SQLite REPLACE Function updating table example

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.

Was this tutorial helpful ?