In PostgreSQL, to get the location of a substring within the specific string, the POSITION() function is used. It is a case-sensitive function that takes two arguments: a substring to be located and a string from which the targeted substring will be searched. The POSITION() function returns a numeric value representing the substring’s location.

This post will show you how to use the Postgres POSITION() function to get the location of a substring. So, let’s start.

How to Use the POSITION() Function in PostgreSQL?

The below snippet illustrates the syntax of the POSITION() function:

POSITION(sub_str IN str);

Here, sub_str is a substring to be searched/located while the str represents a string from which the targeted sub_str will be searched.

Note: The POSITION() function will return ‘0’ if the substring doesn’t exist in the targeted string. It will return NULL if the string or substring argument is NULL.

Example #1: How to Locate a Substring in a String Using the POSITION() Function?

Let’s understand how the POSITION() function works in PostgreSQL:

SELECT POSITION('commandprompt' IN 'Welcome to commandprompt.com');

The POSITION() function will find the substring in the given string. Once the substring is found in the targeted string, then the POSITION() function will return the location of that substring:

The output shows ‘12’, indicating that the substring occurs at the 12th index.

Example #2: Is POSITION() Function Case Sensitive?

Let’s consider the following query that depicts the behavior of the POSITION() function with respect to case sensitivity:

SELECT POSITION('Commandprompt' IN 'welcome to commandprompt.com');

The first letter of the substring is capital, i.e., “Commandprompt” while in the targeted string all the letters are in lowercase, i.e., “welcome to commandprompt.com”. If the POSITION() function returned ‘0’, it means the substring was not found. Otherwise, the POSITION() function will return the substring’s location:

The POSITION() function returned ‘0’, which means that the POSITION() function is a case-sensitive function.

Example #3: Multiple Occurrences of the Substring in a String

Let’s search for a substring that occurs multiple times in the targeted string:

SELECT POSITION('com' IN 'welcome to commandprompt.com');

This time we specified ‘com’ as a substring to the POSITION() function. The ‘com’ substring occurs three times in the targeted string. The POSITION() function will return the location of only the first occurrence as shown in the following snippet:

The output shows ‘4’, indicating that the first occurrence of the specified substring is at the 4th index.

Example #4: How to Use the POSITION() Function on Table’s Data?

We created a table named ‘employee_info’. Let’s run the SELECT statement to see the details of the employee_info table:

SELECT * FROM employee_info
ORDER BY employee_id ASC;

Let’s apply the POSITION() function on the emplooye_info table to locate the position of a substring “Author”:

SELECT POSITION('Author' IN employee_designation)
FROM employee_info
ORDER BY employee_id ASC;

The substring “Author” isn't found in the third and fifth row, so the POSITION() function returns ‘0’. The rest of the rows contain the desired substring, so the POSITION() function returns the location where the substring occurs.

Conclusion

In PostgreSQL, to get the location of a substring within the specific string, the POSITION() function is used. It returns a numeric value representing the substring’s location. It is a case-sensitive function that takes two arguments: a substring to be located and a string from which the targeted substring will be searched. This write-up considered various examples to demonstrate the working of the POSITION() function.