regexp_instr() function

Syntax

int = regexp_instr(varchar input, varchar pattern [, int start_pos 
[, int reference]] [, varchar flags]);

int = regexp_instr(nvarchar input, nvarchar pattern [, int start_pos 
[, int reference]] [, varchar flags]);

The input value specifies the varchar or nvarchar value against which the regular expression is processed.

The pattern value specifies the regular expression. For a description of how to specify Perl compatible regular expression (PCRE) patterns for Unicode data, see any general PCRE documentation or web sources.

The start_pos value specifies the character position at which to start the search for a match. The default is 1.

The reference value indicates a specific instance of the pattern. The default is 1.

For a description of the flags value, see flags argument.

Returns

If there is no match or if there are fewer than reference occurrences of the pattern, the function returns 0. Otherwise, the function returns the index of the matching text item.

Example

select regexp_instr('hello to you', '.o',1,1);
 REGEXP_INSTR
--------------
            4
(1 row)

select regexp_instr('hello to you', '.o',1,2);
 REGEXP_INSTR
--------------
            7
(1 row)