position() function in PostgreSQL

The position() function in PostgreSQL is used to find the location of a substring within a string. It returns the position of the first occurrence of the substring. If the substring is not found, it returns 0.

Syntax – position() function in PostgreSQL

• substring: The substring to search for
• string: The string in which to search

Examples  – position() function in PostgreSQL

1.Find Substring Position:

 

SELECT position('Expert' IN 'PostgreSQL Expert');

Explanation: The substring ‘Expert’ starts at the 12th position in the string ‘ PostgreSQL Expert’.

Output:

2.Substring Not Found

 

SELECT position('wizard' IN 'PostgreSQL Expert');

Explanation: The substring ‘wizard’ is not found in the string  ‘ PostgreSQL Expert’. If the substring is not found it will return Zero , so the output will be

Output:

3. Case Sensitivity

 

SELECT position('expert' IN 'PostgreSQL Expert');

Explanation: The position() function is case-sensitive, so ‘expert’ is not found in the string  ‘ PostgreSQL Expert’. If the substring is not found it will return Zero , so the output will be

Output:

Using position() on a Column in PostgreSQL:

We can use the position() function on a column in a table to find the position of a substring within each row’s value.

We will be using “text” table

 

SELECT *, position('PostgreSQL' IN content) AS position_of_postgresql
FROM Texts;

Explanation

• The query returns the id, content, and the position of the substring ‘PostgreSQL’ in each content.
• If the substring ‘PostgreSQL’  is not found, it returns 0.

 Output:

Using position() function to filter rows based on the presence of string /substring:

You can use the position() function to filter rows based on the presence or absence of a string or  substring.

 

SELECT * FROM Texts
WHERE position('PostgreSQL' IN content) > 0;

• This query selects rows where the substring ‘PostgreSQL’ is found in the content column.

Output: