Home » SQLite String Functions » SQLite replace() Function

SQLite replace() Function

Savigo
You organize your data. Now organize your savings.
Set savings goals, track your progress, and stay on course.
Get Savigo for iPhone 

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)

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;

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
);

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');

Then, query data from the contacts table:

SELECT 
    first_name, 
    last_name,
    phone
FROM 
    contacts;

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');

Finally, query data from the contacts table to verify the update:

SELECT 
    first_name, 
    last_name,
    phone
FROM 
    contacts;

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.

Was this tutorial helpful?