# How to Use the POSITION() Function in PostgreSQL

> In PostgreSQL, to get the substring’s location in a string, the POSITION() function is used. It returns an integer that represents the substring’s location.

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.

---
[View this page online](https://www.commandprompt.com/education/how-to-use-the-position-function-in-postgresql/)