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 within a string.

Here’s 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., updating the dead links and typos.

Please note that SQLite also has a REPLACE statement that is different from the replace() string function.

SQLite replace() function examples

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

1) Basic SQLite replace() function example

The following example replaces the string 'mat' in the sentence 'The cat sat on the mat, mat, mat in the corner of the room.' with the new string 'rug':

SELECT
    replace('The cat sat on the mat, mat, mat in the corner of the room.','mat','rug') result;Code language: SQL (Structured Query Language) (sql)

Output:

result
-----------------------------------------------------------
The cat sat on the rug, rug, rug in the corner of the room.

2) Using replace() function with table data

We will use the replace() function with the UPDATE statement to modify table data.

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

CREATE TABLE contacts (
    contact_id INTEGER 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 rows into the contacts table:

INSERT INTO contacts(first_name, last_name, phone)
VALUES('John','Doe','410-555-0168'),
      ('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)

Output:

first_name  last_name  phone
----------  ---------  ------------
John        Doe        410-555-0168
Lily        Bush       410-444-9862

After that, update the phone area code from the local format (410) to international format (+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 the update:

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

Output:

first_name  last_name  phone
----------  ---------  ---------------
John        Doe        +1-410-555-0168
Lily        Bush       +1-410-444-9862

The output shows that the phone numbers have been updated successfully.

Summary

• Use the replace() function to replace all occurrences of a substring in a string with a new string.