SQLite | SQLite Functions | Replacing Part of a String (replace Function)

Tags:

• SQLite
• Database

The replace function returns a string with part of it replaced by another string. This article explains how to use the function.

Using the replace Function

Use replace to replace part of a string with another string. Its syntax is as follows.

replace(target_string, string_to_replace, replacement_string)

The function returns the first argument with every occurrence of the second argument replaced by the third argument. Note that it does not update the data in the table.

When the first argument is a column, the function returns each stored value with every occurrence of the second argument replaced by the third argument.

–

Let’s replace part of a string. First, create the following table.

create table report (id integer, name text);

sqlite> create table report (id integer, name text);
sqlite> 

Add the following data with INSERT statements.

insert into report values (1, 'report_2018_Seoul');
insert into report values (2, 'report_2018_Daejeon');
insert into report values (3, 'report_2018_Busan');
insert into report values (4, 'report_2017_Paju');

sqlite> insert into report values (1, 'report_2018_Seoul');
sqlite> insert into report values (2, 'report_2018_Daejeon');
sqlite> insert into report values (3, 'report_2018_Busan');
sqlite> insert into report values (4, 'report_2017_Paju');
sqlite> 

Use replace to return the values in the name column with every occurrence of 2018 replaced by 2019.

select name, replace(name, '2018', '2019') from report;

sqlite> .mode column
sqlite> .header on
sqlite> 
sqlite> select name, replace(name, '2018', '2019') from report;
name               replace(name, '2018', '2019')
-----------------  -----------------------------
report_2018_Seoul  report_2019_Seoul            
report_2018_Daeje  report_2019_Daejeon          
report_2018_Busan  report_2019_Busan            
report_2017_Paju   report_2017_Paju             
sqlite> 

The query returns the column values with 2018 changed to 2019.

You can also pass a string directly as the first argument.

select replace('My first birthday', ' ', '_');

sqlite> select replace('My first birthday', ' ', '_');
replace('My first birthday', ' ', '_')
--------------------------------------
My_first_birthday                     
sqlite> 

The spaces (' ') in the string are replaced with underscores (_).