PostgreSQL POSITION() function

POSITION() function

The PostgreSQL position function is used to find the location of a substring within a given string. It returns the position of the first occurrence of the substring within the main string. If the substring is not found, the function returns 0. This function is highly useful for string manipulation and analysis in SQL queries.

Uses of POSITION() Function

• Substring Search: Find the position of a specific substring within a larger string.


• Data Validation: Check if a substring exists within a string for validation purposes.


• Conditional Logic: Implement conditional logic in SQL queries based on the presence of a substring.


• String Analysis: Analyze text data by locating specific patterns or keywords.


• Dynamic Query Construction: Construct dynamic queries that adapt based on the position of substrings.


• Text Processing: Process and manipulate text fields in database records.

Syntax:

POSITION(search_string in main_string) 

Parameters

PostgreSQL Version

• Compatible with PostgreSQL version 9.3 and later.

Visual Presentation of PostgreSQL POSITION() function

Example: PostgreSQL POSITION() function basic usage

The following statement returns the position of the substring ‘our’ within the string ‘w3resource’

Example:

Code:

SELECT POSITION('our' in 'w3resource');

Sample Output:

 position
----------
        6
(1 row)

Example: PostgreSQL POSITION() function using Column

Sample Table: employees.

If we want to display the first name, last name and the position of the substring 'an' within last_name for those rows only where the substirng exists from the employees table, the following SQL can be executed:

Code:

SELECT first_name,last_name,POSITION('an' IN last_name)
FROM employees
WHERE POSITION('an' IN last_name)>0;

Sample Output:

 first_name  | last_name  | position
-------------+------------+----------
 Lex         | De Haan    |        6
 Jose Manuel | Urman      |        4
 Shanta      | Vollman    |        6
 James       | Landry     |        2
 Hazel       | Philtanker |        6
 Louise      | Doran      |        4
 Amit        | Banda      |        2
 Kimberely   | Grant      |        3
 Martha      | Sullivan   |        7
 Nandita     | Sarchand   |        6
 Douglas     | Grant      |        3
(11 rows)

The query will return a result set containing the first_name and last_name columns of employees whose last_name contains the substring 'an'. Additionally, it will provide the position of the substring 'an' within each last_name value.

The WHERE clause filters the results based on whether the position of the substring 'an' in the last_name column is greater than 0, indicating that the substring is present.

Example: Case Sensitivity

Sample Table: employees.

The POSITION() function is case-sensitive. The following example demonstrates this by searching for the substring 'An' in the last_name column:

Code:

SELECT first_name, last_name, POSITION('An' IN last_name)
FROM employees
WHERE POSITION('An' IN last_name) > 0;

Sample Output:

first_name|last_name|position|
----------+---------+--------+
Sundar    |Ande     |       1|

Example: Searching for Substrings at Different Positions

Sample Table: employees.

The POSITION() function is case-sensitive. The following example demonstrates this by searching for the substring 'An' in the last_name column:

Code:

SELECT first_name, last_name, POSITION('ar' IN last_name)
FROM employees
WHERE POSITION('ar' IN last_name) > 0;

Sample Output:

first_name|last_name |position|
----------+----------+--------+
Neena     |Kochhar   |       6|
Ismael    |Sciarra   |       4|
James     |Marlow    |       2|
Peter     |Vargas    |       2|
Karen     |Partners  |       2|
Karen     |Colmenares|       7|
Steven    |Markle    |       2|
Mattea    |Marvins   |       2|
Sundita   |Kumar     |       4|
Nandita   |Sarchand  |       2|
Michael   |Hartstein |       2|

Previous: OVERLAY function
Next: SUBSTRING function