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.

Syntax:

strpos(<string>, < substring >)

PostgreSQL Version: 9.3

Visual 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.

SQL Code:

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

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.

SQL Code:

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)

Previous: SPLIT_PART function
Next: SUBSTR function