Tóm Tắt
PostgreSQL POSITION() function
Last update on August 19 2022 21:51:31 (UTC/GMT +8 hours)
POSITION() function
The PostgreSQL position function is used to find the location of a substring within a specified string.
Syntax:
POSITION(search_string in main_string)
Parameters
Name
Description
search_string
The substring which is to be searched.
main_string
The string in which the position of the substring will be detected.
PostgreSQL Version: 9.3
Pictorial Presentation of PostgreSQL POSITION() function
Example: PostgreSQL POSITION() function
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)
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:
Example:
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)
Previous: OVERLAY function
Next: SUBSTRING function