w3resource
w3resource logo
MySQL Tutorial

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

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.

code:

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

Sample Output:

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

Example of PostgreSQL STRPOS() function using column:

Sample Table: employees


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;

Sample 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)


Amazon promo codes to get huge discounts for limited period (USA only).