PostgreSQL - POSITION Function

The PostgreSQL POSITION() function is used to find the location of a substring within a string. This function is particularly useful for text manipulation and searching operations within your database.

Let us better understand the POSITION Function in PostgreSQL from this article.

Syntax

POSITION(substring IN string)

Parameters:

Let's analyze the above syntax:

• substring: The substring that you want to locate within the string.
• string: The string in which you are searching for the substring.

Return Value:

The POSITION() function returns the location of the searched substring in integer form that represents the location of the substring within the string. It returns zero (0) if no matching substring found.

PostgreSQL POSITION Function Examples

Let us take a look at some of the examples of POSITION Function in PostgreSQL to better understand the concept.

Example 1: Finding the Position of a Substring

The following statement returns the position of the 'Tutorial' in the string 'GeeksForGeeks Tutorial':

SELECT POSITION('Tutorial' IN 'GeeksForGeeks Tutorial');

Output:

Explanation: This indicates that the substring 'Tutorial' starts at the 13th position of the string 'GeeksForGeeks Tutorial'.

Example 2: Finding the First Instance of a Substring

The POSITION() function returns the location of the first instance of the substring in the strings shown in the below example:

SELECT POSITION('am' IN 'I am a geek');

Output:

Explanation: Here, the substring 'am' starts at the 3rd position of the string 'I am a geek'.

Important Points About PostgreSQL POSITION Function

• The POSITION() function is case-sensitive. If the case of the substring and the string do not match, the function will return 0.
• Unlike some programming languages that use zero-based indexing, PostgreSQL POSITION() returns a one-based index, meaning the first character of the string is at position 1.
• POSITION() only returns the position of the first occurrence of the substring.
• POSITION() can be effectively combined with other PostgreSQL string functions like SUBSTRING(), LENGTH(), and TRIM() for more complex string manipulations.