How STRPOS() Works in PostgreSQL

In PostgreSQL, the strpos() function returns the first starting position of a given substring within a string.

We pass the string as the first argument and the substring as the second.

Example

Here’s an example to demonstrate:

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

Result:

It’s the same result we’d get if we were to use the position() function:

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

Result:

No Match

If the substring doesn’t exist in the string, then the function returns zero:

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

Result:

In this case I used the same substring, except this time it was in lowercase. This resulted in 0 being returned due to no match.

Null Arguments

If any of the arguments is null, then null is returned:

SELECT 
    strpos('PostgreSQL', null) AS "1",
    strpos(null, 'SQL') AS "2",
    strpos(null, null) AS "3";

Result:

  1   |  2   |  3   
------+------+------
 null | null | null

Wrong Argument Type

Both arguments need to be text. Passing the wrong type results in an error:

SELECT strpos('PostgreSQL', 3);

Result:

ERROR:  function strpos(unknown, integer) does not exist
LINE 1: SELECT strpos('PostgreSQL', 3);
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

And:

SELECT strpos(123, 'SQL');

Result:

ERROR:  function strpos(integer, unknown) does not exist
LINE 1: SELECT strpos(123, 'SQL');
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

But of course, we can provide the text representation of such values:

SELECT strpos('1234567', '56');

Result: