PostgreSQL has a POSITION() function that returns the first starting index of a specified substring within a string.

If the substring doesn’t exist in the string, then zero is returned.

Syntax

The syntax goes like this:

position ( substring text IN string text )

Examples

Here’s an example to demonstrate:

SELECT POSITION('and' IN 'Two Hands');

Result:

6

As mentioned, if the substring isn’t found in the string, zero is returned:

SELECT POSITION('squid' IN 'Two Hands');

Result:

0

Null Arguments

Null values return null:

\pset null '<null>'
SELECT 
    POSITION(null IN 'Two Hands') AS "1",
    POSITION('and' IN null) AS "2";

Result:

   1    |   2    
--------+--------
 <null> | <null>

Omitting the Argument

Omitting the argument results in an error:

SELECT POSITION();

Result:

ERROR:  function pg_catalog.position() does not exist
LINE 1: SELECT POSITION();
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.