PostgreSQL POSITION() function
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
- New Content published on w3resource:
- HTML-CSS Practical: Exercises, Practice, Solution
- Java Regular Expression: Exercises, Practice, Solution
- Scala Programming Exercises, Practice, Solution
- Python Itertools exercises
- Python Numpy exercises
- Python GeoPy Package exercises
- Python Pandas exercises
- Python nltk exercises
- Python BeautifulSoup exercises
- Form Template
- Composer - PHP Package Manager
- PHPUnit - PHP Testing
- Laravel - PHP Framework
- Angular - JavaScript Framework
- Vue - JavaScript Framework
- Jest - JavaScript Testing Framework