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.

Uses of STRPOS() Function
  • Finding Substring Position: Locate the starting position of a substring within a string.

  • String Analysis: Determine the occurrence of specific patterns or substrings.

  • Conditional Queries: Filter rows based on the presence of a substring within a column.

  • Data Validation: Check for the presence and position of required substrings in data fields.

  • Substring Counting: Identify multiple occurrences and their positions within a string.


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.


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

Sample Output:

 Position of substring
(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

Follow us on Facebook and Twitter for latest update.