PostgreSQL POSITION() function
The PostgreSQL position function is used to find the location of a substring within a given string. It returns the starting position of the substring within the string, or 0 if the substring is not found.
POSITION(search_string in main_string)
|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’
SELECT POSITION('our' in 'w3resource');
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:
SELECT first_name,last_name,POSITION('an' IN last_name) FROM employees WHERE POSITION('an' IN last_name)>0;
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.
- Weekly Trends
- Python Interview Questions and Answers: Comprehensive Guide
- Scala Exercises, Practice, Solution
- Kotlin Exercises practice with solution
- MongoDB Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - JOINS
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook