SQLite | SQLite Functions | Extracting Part of a String (substr Function)
The substr function returns a substring of a specified length from a specified position. This article explains how to use it.
Using the substr Function
The syntax is as follows.
substr(string, start_position, length)
substr(string, start_position)
The function extracts a substring from its first argument, beginning at the position in the second argument and continuing for the length in the third argument. If the third argument is omitted, it returns everything from the start position to the end of the string.
Positions begin at 1 for the first character and 2 for the second. Negative positions count backward: -1 is the last character and -2 is the second-to-last character.
substr('database', 1, 3); /* dat */
substr('database', 1, 4); /* data */
substr('database', 5, 3); /* bas */
substr('database', 5); /* base */
substr('database', -3, 2); /* as */
When a column name is supplied as the first argument, the function extracts a substring from each stored value.
–
Create a table for the example.
create table user (id integer, name text, address text);
sqlite> create table user (id integer, name text, address text);
sqlite>
Insert the following data.
insert into user values (1, 'devkuma', '01:Seoul ');
insert into user values (2, 'kimkc', '02:Busan');
insert into user values (3, 'araikuma', '03:Daejeon');
insert into user values (4, 'happykuma', '01:Seoul ');
insert into user values (5, 'raccoon', '04:Paju ');
sqlite> insert into user values (1, 'devkuma', '01:Seoul ');
sqlite> insert into user values (2, 'kimkc', '02:Busan');
sqlite> insert into user values (3, 'araikuma', '03:Daejeon');
sqlite> insert into user values (4, 'happykuma', '01:Seoul ');
sqlite> insert into user values (5, 'raccoon', '04:Paju ');
sqlite>
Use substr to extract three characters beginning with the fourth character of each value in the address column.
select id, name, substr(address, 4, 3) from user;
sqlite> .mode column
sqlite> .header on
sqlite>
sqlite> select id, name, substr(address, 4, 3) from user;
id name substr(address, 4, 3)
---------- ---------- ---------------------
1 devkuma Seo
2 kimkc Bus
3 araikuma Dae
5 raccoon Paj
4 happykuma Seo
sqlite>
The result contains a substring of the specified length beginning at the specified position.
Next, omit the length to extract everything from the fourth character to the end.
select id, name, substr(address, 4) from user;
sqlite> select id, name, substr(address, 4) from user;
id name substr(address, 4)
---------- ---------- ------------------
1 devkuma Seoul
2 kimkc Busan
3 araikuma Daejeon
5 raccoon Paju
4 happykuma Seoul
sqlite>
The result contains the substring from the specified position through the end of each value.