PostgreSQL POSITION() Function

The PostgreSQL POSITION() function returns the location of the first occurrence of a specified substring in a given string. If the substring is not found within the given string, this function returns 0.

This function performs a case-sensitive search. Note that the first position in string starts with 1.

Syntax

POSITION(substring in string)

Parameters

Return Value

Returns the location of the first occurrence of the substring in the string.

Example 1:

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

SELECT POSITION('A' in 'AlphaCodingSkills.com');
Result: 1

SELECT POSITION('Coding' in 'AlphaCodingSkills.com');
Result: 6

SELECT POSITION('.com' in 'AlphaCodingSkills.com');
Result: 18

SELECT POSITION('Z' in 'AlphaCodingSkills.com');
Result: 0

SELECT POSITION('ABC' in 'AlphaCodingSkills.com');
Result: 0

Example 2:

Consider a database table called Employee with the following records:

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

SELECT *, 
SUBSTR(PhoneNumber, 1, POSITION('-' in PhoneNumber) - 1) AS CountryCode 
FROM Employee;

This will produce the result as shown below: