SQLite SUBSTR() Function

The SQLite SUBSTR() function is used to extract a substring from a string starting from specified position.

The SUBSTR() function is a synonym of the SUBSTRING() function.

Syntax

SUBSTR(string, start, length)

Parameters

`string`	`Required.` Specify the string to extract from. If it is a string then characters indices refer to actual UTF-8 characters. If it is a BLOB then the indices refer to bytes.
`start`	`Required.` Specify the start position. It can be both a positive or negative number. If it is a positive number, this function extracts from the beginning of the string. If it is a negative number, this function extracts from the end of the string. Note that the first position in string starts with 1.
`length`	`Optional.` Specify the number of characters to extract. If omitted, the whole string will be returned (from the start position). If it is a negative number then abs(length) characters preceding the start character are returned.

Return Value

Returns the substring extracted from specified string.

Example 1:

The example below shows the usage of SUBSTR() function.

SELECT SUBSTR('AlphaCodingSkills.com', 1);
Result: 'AlphaCodingSkills.com'

SELECT SUBSTR('AlphaCodingSkills.com', 6);
Result: 'CodingSkills.com'

SELECT SUBSTR('AlphaCodingSkills.com', 6, 6);
Result: 'Coding'

SELECT SUBSTR('AlphaCodingSkills.com', 6, -5);
Result: 'Alpha'

SELECT SUBSTR('AlphaCodingSkills.com', -4, 4);
Result: '.com'

SELECT SUBSTR('AlphaCodingSkills.com', -4, -6);
Result: 'Skills'

Example 2:

Consider a database table called Employee with the following records:

PhoneNumber	EmpID	Address
+33-147996101	1	Grenelle, Paris, France
+31-201150319	2	Geuzenveld, Amsterdam, Netherlands
+86-1099732458	3	Yizhuangzhen, Beijing, China
+65-67234824	4	Yishun, Singapore
+81-357799072	5	Koto City, Tokyo, Japan

In the query below, the SUBSTR() function is used to extract the country code from the PhoneNumber column records.

SELECT *, SUBSTR(PhoneNumber, 2, 2) AS CountryCode 
FROM Employee;

This will produce the result as shown below:

PhoneNumber	EmpID	Address	CountryCode
+33-147996101	1	Grenelle, Paris, France	33
+31-201150319	2	Geuzenveld, Amsterdam, Netherlands	31
+86-1099732458	3	Yizhuangzhen, Beijing, China	86
+65-67234824	4	Yishun, Singapore	65
+81-357799072	5	Koto City, Tokyo, Japan	81

❮ SQLite Functions