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.

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)

Previous: SPLIT_PART function
Next: SUBSTR function