strpos()

A system function for returning the start of a string within another string

strpos() is a system function which returs the start position of a string within another string.

strpos() was added in PostgreSQL 6.2.

Usage

strpos ( string text, substring text ) → integer

strpos() returns the position of the specified substring as an integer, or 0 if the substring is not found.

strpos() is case-sensitive, but case-insensitive searches can be performed by casting its arguments to citext.

Basic usage of strpos():

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

strpos() is case-sensitive, and returns 0 if the specified substring is not found:

postgres=# SELECT strpos('foobar', 'BAR');
 strpos 
--------
      0
(1 row)

Use the citext data type for case-insensitive searches:

postgres=# SELECT strpos('foobar'::citext, 'BAR'::citext);
 strpos 
--------
      4
(1 row)

strpos() works with multibyte characters:

postgres=# SELECT strpos('ほげほげ', 'げほ');
 strpos 
--------
      2
(1 row)