In PostgreSQL, to find the position of a substring within a string we will be using the POSITION() function. This function returns the position of the first occurrence of the specified substring within the given string. If the substring is not found, it will return 0. We can also use another function STRPOS(). Lets look at example of
- How to get position of substring in PostgreSQL using POSITION() function
- How to get position of substring using STRPOS() function
Syntax of Position() Function:
POSITION(substring IN string)
- substring: The substring to search for.
- string: The string or column to search within.
Examples
Example 1: Finding the Position of a Substring in a Specific String in PostgreSQL
To find the position of ‘SQL’ in the string ‘PostgreSQL’:
SELECT POSITION('SQL' IN 'PostgreSQL') AS substring_position;
Output:
Example 2: Substring Not Found– Position()
If the substring is not found, the function returns 0:
SELECT POSITION('Database' IN 'PostgreSQL') AS substring_position;
Output:
Example 3: Location or position of substring in Postgresql:
We will be using the table summerfruits
To get Location or position of substring in PostgreSQL column we use POSITION() function. With two arguments first argument is substring and second argument is string column as shown below.
select *,position('e' in colour) from summerfruits
Position of substring ‘e’ is returned from Colour column. So the resultant table will be
Output:
Get Position of String using STRPOS() function in PostgreSQL:
In PostgreSQL, the strpos() function is used to find the position of the first occurrence of a substring within a string. It returns the index position of the substring, where the first character of the string is at position 1. If the substring is not found, it returns 0.
Syntax of STRPOS() Function:
strpos(string, substring)
- string: The string in which to search.
- substring: The substring to find.
Examples
Example 1: Finding the Position of a Substring in a String – STRPOS()
To find the position of ‘SQL’ in the string ‘PostgreSQL’:
SELECT strpos('PostgreSQL', 'SQL') AS substring_position;
Output:
Example 2: Substring Not Found– STRPOS()
If the substring is not found, the function returns 0:
SELECT strpos('PostgreSQL', 'Database') AS substring_position;
Output:
Example 3: Finding the Position of a substring in a Column
We will be using the table summerfruits
To get Location of substring in PostgreSQL column we use STRPOS() function. With two arguments first argument is string column and second argument is substring as shown below.
select *,STRPOS(colour,'e') from summerfruits
Position of substring ‘e’ is returned from Colour column. So the resultant table will be
Output:
