Simple Oracle REGEXP_INSTR function usage

Simple Oracle REGEXP_INSTR function usage

The REGEXP_INSTR function allows you to search for a regular expression pattern string. The function uses the characters defined in the input character set for string calculation.

It returns an integer indicating the child location of the start or end match, depending on the value of the return_option parameter. If no match is found, the function returns 0.

Syntax

The syntax of the REGEXP_INSTR function in the Oracle database is:

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

Parameters source_char

Character Expression of the search value, which can be any data type CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB or NCLOB.

pattern

Regular Expression

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.
*	Matches zero or multiple.
+	Match one or more.
?	Matches zero or one occurrence.
.	Matches any character, except null.
|	Used like an "OR" to specify more than one alternative.
[ ]	Used to specify a matching list where you are trying to match any one of the characters in the list.
[^ ]	Used to specify a nonmatching list where you are 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 be more than one character.
[::]	Matches character classes.
[==]	Matches equivalence classes.
\d	Match a numeric character.
\D	Match a non-numeric character.
\w	Match any word characters that contain underscores.
\W	Match any non-word characters.
\s	Matches any blank characters, including spaces, tabs, and page breaks.
\S	Match any non-blank characters.
\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, but not more than m times.

position

Optional. Start position of the search string. If omitted, the default value is 1, which is the first position in the string.

occurrence

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

return_option

Optional. Location returned by Oracle:

If 0 is specified, Oracle returns the first character. This is the default one.

If 1 is specified, the location after the character is returned by Oracle.

match_parameter

Optional. It allows you to modify the matching behavior of the REGEXP_INSTR function. 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 have multiple lines, where ^ is the start of a line and $ is the end of a line, regardless of the position of those characters in expression. By default, expression is assumed to be a single line.
‘x’	Whitespace characters are ignored. By default, whitespace characters are matched like any other character.

Examples matches a single character

Next, let's look at the simplest case and find the first "e" character in the string.

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

The following example shows a string where "1" is the start position "2" is the second matching, and "0" is the first character position returned by return_option.

"C" is case sensitive, so 13 will be returned

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

Examples matches multiple characters

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

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

In this example, the first occurrence of 'with' is returned in the string, which matches a phrase.

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

For example:

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

In this example, "my" is searched for at 10 in the string. In this case, it skips the first nine characters in the string before searching.

Examples matches multiple alternatives

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

For example:

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

In this example, 6 is returned because it is the first Vowel (a, I, o, e or u) string to be searched. Because the match_parameter value is not specified,

The REGEXP_INSTR function executes a case-sensitive search, which means the 'I' in "Itmyhome" does not match.