Postgresql position second occurrence

by

Query: PostgreSQL position second occurrence

In PostgreSQL, you can use the position function to find the position of a substring within a string. However, the position function only returns the position of the first occurrence. To find the position of the second occurrence, you can use the strpos function in combination with the substring function. Here is an example:

Example:

Let’s say you have a string 'Hello, world. Hello, everyone.'. You want to find the position of the second occurrence of the word 'Hello'. 


    SELECT strpos(substr('Hello, world. Hello, everyone.', strpos('Hello, world. Hello, everyone.', 'Hello') + 1), 'Hello') + strpos('Hello, world. Hello, everyone.', 'Hello')

The above query uses the strpos function to find the position of the second occurrence of 'Hello'. Let’s break it down:

  1. Inner strpos: strpos('Hello, world. Hello, everyone.', 'Hello') + 1 – This finds the position of the first occurrence of 'Hello' and adds 1 to it, so that we start searching from the position following the first occurrence.
  2. Outer strpos: strpos(substr('Hello, world. Hello, everyone.', strpos('Hello, world. Hello, everyone.', 'Hello') + 1), 'Hello') – This searches the substring of the original string starting from the position following the first occurrence of 'Hello', and finds the position of the second occurrence of 'Hello'.
  3. Addition: strpos(substr('...', ...), '...') + strpos('Hello, world. Hello, everyone.', 'Hello') – Finally, we add the position of the second occurrence to the position of the first occurrence to get the overall position of the second occurrence within the original string.

The result of the above query would be the position of the second occurrence of 'Hello' within the string.

Leave a comment