strpos() function in PostgreSQL

The strpos() 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 – strpos() function in PostgreSQL

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

 
 
Examples  – strpos() function in PostgreSQL

1.Find Substring Position:

 

SELECT strpos('PostgreSQL Expert','Expert');

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

Output:

2. Substring Not Found

 

SELECT strpos('PostgreSQL Expert','wizard');

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 strpos('PostgreSQL Expert','expert');

Explanation: The strpos() 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 strpos() on a Column in PostgreSQL:

We can use the strpos() 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 *, strpos(content, 'PostgreSQL') 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 strpos() function to filter rows based on the presence of string /substring:

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

 

SELECT * FROM Texts
WHERE strpos(content, 'PostgreSQL') > 0;

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

Output: