REGEXP_INSTR

Parameters

stringtext

String to search in

patterntext

Regular expression

startintegeroptional

Position to start the search from, 1 by default

occurrenceintegeroptional

Number of the match, 1 by default

endoptionintegeroptional

0 for the position where the match starts (by default), 1 for the position right after it ends

flagstextoptional

Flags, for example i to ignore case

subexprintegeroptional

Number of the group in parentheses whose position to return; 0 by default, the whole match

Return value

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

Examples

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;

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

Details

See also