REGEXP_INSTR

Syntax

REGEXP_COUNT(str, pattern [, position [, occurrence [, return_option [, match_param [, sub_expr}]]]])

Overview

REGEXP_INSTR is a function that returns the position where the pattern given as a regular expression matches within str.

If it does not match str, it returns 0.

Parameter

Parameter	Description
str	Arbitrary operation that returns a string. It can be of type TEXT or CHAR.
pattern	Arbitrary operation that returns a string written as a regular expression. Arbitrary operation that returns a string. It can be of type TEXT or CHAR.
position	Arbitrary operation that returns a numeric value. It specifies where to start pattern checking.
occurrence	Arbitrary operation that returns a numeric value. It specifies how many times to check the pattern.
return_option	Arbitrary operation that returns a numeric value. 0 : It returns the first position of a string that matches the pattern. 1 : It returns the next position of a string that matches the pattern.
match_param	Arbitrary operation returning a string. It sets how to check for patterns. The following values can be used, and multiple values can be used simultaneously. i : It is not case sensitive. c : It is case sensitive. n : Periods (.) include line breaks. m : The input string is more than one line. x : This ignores whitespace characters in the pattern.
sub_expr	Arbitrary operation that returns a numeric value. It can be used from 0 to 9. sub_expr assigns numbers to each group enclosed in parentheses in pattern from left to right.

Example

SELECT REGEXP_INSTR('abcabcabc','abc', 2);
 regexp_instr 
--------------
            4
(1 row)