PostgreSQL sometimes provides us with multiple functions that basically do the same thing. Such is the case with the following two functions that return the position of a given substring within a string.

The POSITION() Function

The position() function returns the starting index of the specified substring within a string.

Example:

SELECT position('SQL' in 'PostgreSQL');

Result:

8

We can see that SQL exists within PostgreSQL and so we get the starting index of that substring (SQL) within the string (PostgreSQL).

If there’s no match, zero is returned:

SELECT position('sql' in 'PostgreSQL');

Result:

0

This time I passed sql in lowercase, and so it didn’t match the (uppercase) SQL that’s in the string.

The STRPOS() Function

The strpos() function does basically the same thing that position() does, except with a slightly different syntax.

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

Result:

8

So with strpos(), we pass the string first, followed by the substring. We also don’t need to use the in keyword (like we did with the position() function).

And just like position(), the strpos() function returns zero if there’s no match:

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

Result:

0