PostgreSQL POSITION Function

Education is not limited to just classrooms. It can be gained anytime, anywhere... - Ravi Ranjan (M.Tech-NIT)

PostgreSQL POSITION() function

POSITION() function

The PostgreSQL position function is used to find the location of a substring within a specified string.

Syntax :

POSITION(search_string in main_string) 

Parameters

Name Description
search_string The substring which is to be searched.
main_string The string in which the position of the substring will be detected.

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 :

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

Output :

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

PostgreSQL POSITION() function using Column :

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;

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)