Simple usage of Oracle's REGEXP_INSTR function

The REGEXP_INSTR function lets you search for a regular expression pattern string. The function uses the characters defined by the input character set for string calculations.

It returns an integer that indicates the start or end of the matching sub-position, depending on the value of the Return_option parameter. If no match is found, the function returns 0.

Grammar

The syntax for the REGEXP_INSTR function in an Oracle database is:

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

Parameters Source_char

A character expression that searches for a value, which can be any data type Char,varchar2,nchar,nvarchar2,clob or NCLOB.

pattern

Regular expressions

Value	Description
^	Matches the beginning of a string. If used with a match_parameter of ' m ', it matches the start of a line anywhere within expression.
$	Matches the end of a string. If used with a match_parameter of ' m ', it matches the end of a line anywhere within expression.
*	Match 0 or more.
+	Match one or more occurrences.
?	Match 0 or one occurrence.
.	Matches any character, except NULL.
|	Used like a "OR" to specify more than one alternative.
[ ]	Used to specify a matching list where is trying to match any one of the characters in the list.
[^ ]	Used to specify a nonmatching list where is trying to match any character except for the ones in the list.
( )	Used to group expressions as a subexpression.
{m}	Matches m times.
{m,}	Matches at least m times.
{M,n}	Matches at least m times, but no more than n times.
\ n	N is a number between 1 and 9. Matches the nth subexpression found within () before encountering \ n.
[..]	Matches one collation element that can is more than one character.
[::]	Matches character classes.
[==]	Matches equivalence classes.
\d	Matches a numeric character.
\d	Matches a non-numeric character.
\w	Matches any word character that includes an underscore.
\w	Matches any non-word character.
\s	Matches any whitespace character, including spaces, tabs, page breaks, and so on.
\s	Matches any non-whitespace character.
\a	Matches the beginning of a string or Matches at the end of a string before a newline character.
\z	Matches at the end of a string.
*?	Matches the preceding pattern zero or more occurrences.
+?	Matches the preceding pattern one or more occurrences.
??	Matches the preceding pattern zero or one occurrence.
{n}?	Matches the preceding pattern n times.
{N,}?	Matches the preceding pattern at least n times.
{n,m}?	Matches the preceding pattern at least n times, and not more than m times.

position

Optional. The start position of the search in the string. If omitted, the default is 1, which is the first position in a string.

Occurrence

Optional. It is the nth matching position in a pattern string. If omitted, the default is 1.

return_option

Optionally specify the location that Oracle returns:

If you specify 0, Oracle returns the position of the first character that appears. This is the default.

If 1 is specified, Oracle returns the position after the character has been returned.

Match_parameter

Optional. It allows you to modify the behavior of regexp_instr function matching. It can be a combination of the following:

Value	Description
C	Case-sensitive matching.
I	Case-insensitive matching.
N	Allows the period character (.) to match the newline character. By default, the period is a wildcard.
' m '	expression is assumed to has multiple lines, where ^ is the start of a line and $ are the end of a line, Regardle SS of the position of those characters in expression. By default, expression is assumed-be-a single line.
' X '	whitespace characters is ignored. By default, whitespace characters is matched like any other character.

Examples matches a single character

In the simplest case, find the position of the first "E" character in the string.

SELECT REGEXP_INSTR (‘hello itmyhome‘, ‘e‘)FROM dual;-- Result: 2

The following example gives a string, "1" for the start position "2" is the search for the second match, "0" is the first character position that Return_option returns

"C" is case-sensitive, so it will return 13

SELECT REGEXP_INSTR (‘my is itMyhome‘, ‘m‘, 1, 2, 0, ‘c‘)FROM dual;-- Result: 13

Examples Match multiple characters

We will use the REGEXP_INSTR function to match the multi-character pattern.

SELECT REGEXP_INSTR (‘World filled with love‘, ‘with‘, 1, 1, 0, ‘i‘)FROM dual;

This example returns the first occurrence of ' with ' in the string, which will match a phrase.

We can change the start position of the search so that we perform the search starting from the middle of the string.

For example:

SELECT REGEXP_INSTR (‘my name is itmyhome‘, ‘my‘, 10, 1, 0, ‘i‘)FROM dual;

This example will start by searching for "my" position in the string 10. In this case, before the search, it skips the first 9 characters in the string.

Examples matches multiple alternatives

In the following example, we will use | Mode. The | mode is used to specify multiple alternatives like a "or".

For example:

SELECT REGEXP_INSTR (‘Itmyhome‘, ‘a|i|o|e|u‘)FROM dual;-- Result: 6

This example returns 6 because it is the first vowel (a,i,o,e or u) string to be searched. Since we did not specify the Match_parameter value,

The REGEXP_INSTR function performs a case-sensitive search, which means that the ' I ' in ' Itmyhome ' will not match.

Itmyhome

Simple usage of Oracle's REGEXP_INSTR function