regexp_instr()

A function returning the position in a string where a regular expression occurs
This entry relates to a PostgreSQL feature which is part of PostgreSQL 15, due to be released in late 2022.

regexp_instr() is a system function function returning the position in a string where the specified occurrence of a POSIX regular expression is located.

regexp_instr() was added in PostgreSQL 15.

Usage

regexp_instr ( string text, pattern text [, start integer [, N integer [, endoption integer [, flags text [, subexpr integer ] ] ] ] ] ) → integer

A list of flags which can be used with regexp_instr() is available in the PostgreSQL documentation: ARE Embedded-Option Letters.

Change history

Examples

Basic usage example for regexp_instr(), here determining the starting position of the first occurrence of "ooo":

postgres=# SELECT regexp_instr('foobarboo flooobilooo', 'o{3,}');
 regexp_instr 
--------------
           13
(1 row)

Determining the starting position of the first occurrence of "ooo" beginning with the 14th character in the string:

postgres=# SELECT regexp_instr('foobarboo flooobilooo', 'o{3,}', 14);
 regexp_instr 
--------------
           19
(1 row)

Categories

String manipulation, System function

See also

regexp_count(), regexp_like(), regexp_substr(), regexp_match(), regexp_matches(), regexp_replace()