Categories:

String Functions (Regular Expressions)

REGEXP_INSTR

Returns the position of the specified occurrence of the regular expression pattern in the string subject. If no match is found, returns 0.

See also String Functions (Regular Expressions).

Syntax

REGEXP_INSTR( <subject> , <pattern> [ , <position> , <occurrence> , <option> , <parameters> ] )

Arguments

Required:

subject

Subject to match.

pattern

Pattern to match.

Optional:

position

Number of characters from the beginning of the string where the function starts searching for matches.

Default: 1 (the search for a match starts at the first character on the left)

occurrence

Specifies which occurrence of the pattern to match. The function skips the first occurrence - 1 matches.

Default: 1

option

Specifies whether to return the offset of the first character of the match (0) or the offset of the first character following the end of the match (1).

Default: 0

parameters

String of one or more characters that specifies the regular expression parameters used for searching for matches. The supported values are:

c , i , m , e , s

For more details, see Parameters.

Default: c

Note

By default, REGEXP_INSTR returns the begin or end character offset for the entire matching part of subject. However, if the e (for “extract”) parameter is specified, REGEXP_INSTR returns the begin or end character offset for the part of the subject that matches the first sub-expression in the pattern. If there is no sub-expression in the pattern, REGEXP_INSTR behaves as if e was not set. If there is more than one sub-expression in the pattern, the additional sub-expressions have no effect. For an example, see Examples in this topic.

Usage Notes

Collation Details

Arguments with collation specifications are currently not supported.

Examples

The following example matches occurrences of the word was. Matching begins at the 1st character in the string and returns the position in the string of the character following the first occurrence:

select regexp_instr('It was the best of times, it was the worst of times', '\\bwas\\b', 1, 1) as "result" from dual;

+--------+
| result |
|--------|
|      4 |
+--------+

The following example returns the offset of the first character of the part of the string that matches the pattern. Matching begins at the 1st character in the string and returns the first occurrence of the pattern:

select regexp_instr('It was the best of times, it was the worst of times', 'the\\W+(\\w+)',1,1,0) as "result" from dual;

+--------+
| result |
|--------|
|      8 |
+--------+

The following example is the same as the previous example, but uses the e parameter to return the character offset for the part of the subject that matches the first sub-expression in the pattern (i.e. the first set of word characters after the):

select regexp_instr('It was the best of times, it was the worst of times', 'the\\W+(\\w+)',1,1,0,'e') as "result" from dual;

+--------+
| result |
|--------|
|     12 |
+--------+

The following example matches occurrences of words ending in st preceded by 2 or more alphabetic characters (case-insensitive). Matching begins at the 15th character in the string and returns the position in the string of the character following the first occurrence:

select regexp_instr('It was the best of times, it was the worst of times', '[[:alpha:]]{2,}st', 15, 1) as "result" from dual;

+--------+
| result |
|--------|
|     38 |
+--------+