PostgreSQL SUBSTRING() Function

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

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

Syntax

/* version 1 */
SUBSTRING(string, start, length)

/* version 2 */
SUBSTRING(string FROM start FOR length)

Parameters

`string`	`Required.` Specify the string to extract from.
`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).

Return Value

Returns the substring extracted from specified string.

Example 1:

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

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

SELECT SUBSTRING('AlphaCodingSkills.com' FROM 1);
Result: 'AlphaCodingSkills.com'

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

SELECT SUBSTRING('AlphaCodingSkills.com' FROM 6);
Result: 'CodingSkills.com'

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

SELECT SUBSTRING('AlphaCodingSkills.com' FROM 6 FOR 6);
Result: 'Coding'

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

SELECT SUBSTRING('AlphaCodingSkills.com' FROM -4 FOR 4);
Result: '.com'

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 SUBSTRING() function is used to extract the country code from the PhoneNumber column records.

SELECT *, SUBSTRING(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

❮ PostgreSQL Functions