REGEXP_INSTR

Returns the position of a regular expression match in a string.

PostgreSQL 17.9

REGEXP_INSTR(string, pattern[, start[, occurrence[, endoption[, flags[, subexpr]]]]])

[ ]optional part

string: String to search in
text
pattern: Regular expression
text
start: Position to start the search from, 1 by default
integerOptional
occurrence: Number of the match, 1 by default
integerOptional
endoption: 0 for the position where the match starts (by default), 1 for the position right after it ends
integerOptional
01
flags: Flags, for example i to ignore case
textOptional
bceimnpqstwx
subexpr: Number of the group in parentheses whose position to return; 0 by default, the whole match
integerOptional

Return value

An integer position starting from 1; 0 if there is no match

Examples

Match positions

PostgreSQL 17.9

SELECT REGEXP_INSTR('a1b22c333', '[0-9]+') AS FIRST,
	REGEXP_INSTR('a1b22c333', '[0-9]+', 1, 2) AS SECOND,
	REGEXP_INSTR('a1b22c333', '[0-9]+', 1, 2, 1) AS after_second,
	REGEXP_INSTR('abc', '[0-9]') AS not_found;

first	second	after_second	not_found
2	4	6	0

Where the last name starts

PostgreSQL 17.9

SELECT member_name,
	REGEXP_INSTR(member_name, '[A-Z]', 1, 2) AS last_name_start
FROM FamilyMembers;

member_name	last_name_start
Headley Quincey	9
Flavia Quincey	8
Andie Quincey	7
Lela Quincey	6
Annie Quincey	7
Ernest Forrest	8
Constance Forrest	11
Wednesday Addams	11

Details

The function appeared in PostgreSQL 15. Positions start from 1. To find a plain substring without a pattern, STRPOS or POSITION is simpler.

REGEXP_INSTR

Return value

Examples

Match positions

Where the last name starts

Details

See also