PostgreSQL STRPOS Function

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

PostgreSQL STRPOS() function

STRPOS() function

The PostgreSQL strpos() function is used to find the position, from where the substring is being matched within the string.

Syntax :

strpos(<string>, < substring >)

PostgreSQL Version : 9.3

Pictorial Presentation of PostgreSQL STRPOS() function

Example of PostgreSQL STRPOS() function :

In the example below, the position of the specified substring 'so' within the string as specified in the first parameter within the argument is 5.

SELECT strpos('w3resource', 'so')AS "Position of substring";

Output :

 Position of substring
-----------------------
                     5
(1 row)

Example of PostgreSQL STRPOS() function using column :

If we want to display the employee_id, first_name, last_name and the position of a specific substring 'lia', which must exists within the column first_name from employees table, the following SQL can be used.

SELECT employee_id,first_name,last_name,
strpos(first_name,'lia') AS "Position of lia" 
FROM employees 
WHERE strpos(first_name,'lia')>0;

Output :

 employee_id | first_name | last_name | Position of lia
-------------+------------+-----------+-----------------
         125 | Julia      | Nayer     |               3
         171 | William    | Smith     |               4
         186 | Julia      | Dellinger |               3
         206 | William    | Gietz     |               4
(4 rows)