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 stringreplacement
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()
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:
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)
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.