SQL Reference - REGEXP

REGEXP_INSTR(string, pattern, start, occurrence, return_end, match_param)

string: This parameter represents the string in which the search is performed. It could be any of the data type CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.
pattern: This denotes the regular expression to be searched. For any character other than a multibyte character, this has to be a VARCHAR2 expression.
start: Defines the position in the string where the search is to begin. It is 1 by default. If the value is negative, the function counts back from the end of the string and begins the search.
occurrence: Specifies which occurrence of the pattern the function should search for. By default, it is set to 1, meaning the function will look for the first instance of the pattern.
return_end: It controls the value that the function returns. When set to 0, it returns the beginning position of the match. When set to 1, it returns the position of the character following the match. Default is 0.
match_param: A string of characters allowing control over the match behavior. It can include ‘c’ for case-sensitive matching, ‘n’ for allowing match-any-character operator to match the newline character, ‘m’ for treating the source string as multiple lines, ‘x’ for ignoring whitespace characters, ‘i’ for case-insensitive matching.

Example

SELECT REGEXP_INSTR('Oracle Example', 'a') FROM dual;

Output

Explanation

The REGEXP_INSTR function in this example is used to return the position of the first occurrence of ‘a’ in the string ‘Oracle Example’. As indexing starts from 1, and ‘a’ is the second letter in the phrase, the output is ‘2’.

REGEXP_INSTR(expr, pat[, pos[, occurrence[, return_end[, match_type]]]])

expr: This refers to the string to search. It is the subject of the regular expression.
pat: Represents the pattern to search for in the expr. This is the regular expression.
pos: This is an optional integer parameter to specify the initial position to start the search in the string expr. The default value is 1, meaning it starts the search at the beginning of the string.
occurrence: An optional parameter to specify which occurrence of the pattern to search for. Default value is 1, it looks for the first occurrence.
return_end: An optional parameter that governs what the function returns when it succeeds - if set to 1, it returns the position of the end of the match, otherwise, it returns the beginning of the match. Default value is 0.
match_type: An optional parameter that specifies how the function matches the pattern in the string expr. There are different match types like ‘c’ for case sensitive, ‘i’ for case insensitive etc.

Example

SELECT REGEXP_INSTR('SQL is a programming language', '[a-z]{7}') AS MatchPosition;

Output

+---------------+
| MatchPosition |
+---------------+
|             5 |
+---------------+

Explanation

The REGEXP_INSTR function in the example searches for the first occurrence of any lowercase word of exactly 7 letters in the string ‘SQL is a programming language’. The function returns the position of the first character of the first match, which is at the 5th position in the input string, corresponding to ‘is a pr’.