PostgreSQL POSITION() function

POSITION() function

The PostgreSQL position function is used to find the location of a substring within a given string. It returns the starting position of the substring within the string, or 0 if the substring is not found.

Syntax:

POSITION(search_string in main_string) 

Parameters

PostgreSQL Version: 9.3

Pictorial Presentation of PostgreSQL POSITION() function

Example: PostgreSQL POSITION() function

The following statement returns the position of the substring ‘our’ within the string ‘w3resource’

Example:

Code:

SELECT POSITION('our' in 'w3resource');

Sample Output:

 position
----------
        6
(1 row)

PostgreSQL POSITION() function using Column :

Sample Table: employees.

If we want to display the first name, last name and the position of the substring 'an' within last_name for those rows only where the substirng exists from the employees table, the following SQL can be executed:

Example:

SELECT first_name,last_name,POSITION('an' IN last_name)
FROM employees
WHERE POSITION('an' IN last_name)>0;

Sample Output:

 first_name  | last_name  | position
-------------+------------+----------
 Lex         | De Haan    |        6
 Jose Manuel | Urman      |        4
 Shanta      | Vollman    |        6
 James       | Landry     |        2
 Hazel       | Philtanker |        6
 Louise      | Doran      |        4
 Amit        | Banda      |        2
 Kimberely   | Grant      |        3
 Martha      | Sullivan   |        7
 Nandita     | Sarchand   |        6
 Douglas     | Grant      |        3
(11 rows)

The query will return a result set containing the first_name and last_name columns of employees whose last_name contains the substring 'an'. Additionally, it will provide the position of the substring 'an' within each last_name value.

The WHERE clause filters the results based on whether the position of the substring 'an' in the last_name column is greater than 0, indicating that the substring is present.

Previous: OVERLAY function
Next: SUBSTRING function