position()

A function for returning the position of a value in a string

position() is a system function for returning the first position of a value in a string.

position() was added in PostgreSQL 6.2.

Usage

position ( substring text IN string text ) → integer

0 is returned if the provided substring is not contained within the provided string.

strpos() provides equivalent functionality with the regular function invocation syntax.

Change history

Examples

Basic usage example for position():

postgres=# SELECT position('bar' IN 'foobar');
 position 
----------
        4
(1 row)

This is equivalent to:

postgres=# SELECT strpos('foobar', 'bar');
 strpos 
--------
      4
(1 row)

0 is returned if the provided string is not found:

postgres=# SELECT position('baz' IN 'foobar');
 position 
----------
        0
(1 row)

position() works with multibyte strings:

postgres=# SELECT position('げ' IN 'ほげほげ');
 position 
----------
        2
(1 row)

Categories

String manipulation, System function

See also

strpos()