PostgreSQL SUBSTR() Function

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

Syntax

SUBSTR(string, start, length)

Parameters

`string`	`Required.` Specify the string to extract from.
`start`	`Required.` Specify the start position of search in the string.
`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 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'

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

❮ PostgreSQL Functions