Skip Headers
Oracle® OLAP Expression Syntax Reference
Release 11.2

Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Go to next page
PDF · Mobi · ePub


REGEXP_INSTR searches a string for a regular pattern. It can return an integer indicating either the beginning or the ending position of the matched substring. If no match is found, the function returns 0.

The function evaluates strings using characters as defined by the input character set.

Return Value



REGEXP_INSTR (source_char, pattern
              [, position
                 [, occurrence
                    [, return_option
                       [, match_parameter ]


source_char is the text expression to search.

pattern is the string to search for. A period matches any character. For a list of operators, refer to the Oracle Database SQL Language Reference, Appendix D, Oracle Regular Expression Support.

position is a nonzero integer indicating the character of source_char where the function begins the search. When position is negative, then the function counts and searches backward from the end of string. The default value of position is 1, which means that the function begins searching at the first character of source_char.

occurrence is an integer indicating which occurrence of pattern the function should search for. The value of occurrence must be positive. The default values of occurrence is 1, meaning the function searches for the first occurrence of pattern.

return_option is either 0 to return the position of the match (default), or 1 to return the position of the character following the match.

match_parameter is a text literal that lets you change the default matching behavior of the function. You can specify one or more of the following values:


REGEXP_INSTR('Mississippi', 'i', 1, 3) searches the string Mississippi for the third instance of the letter i, beginning the search at the first letter. It returns the value 8.