REGEXP_INSTR function

REGEXP_INSTR returns the location of a pattern in a given string. REGEXP_INSTR allows the searches of regular expressions. The simplest form of this function is:

REGEXP_INSTR(source_string, pattern_to_find)

The general format for the REGEXP_INSTR function with all the options is([] means optional):


REGEXP_INSTR(source_string, 
             pattern_to_find [, 
             position, 
             occurrence, 
             return_option, 
             match_parameter])
  • source_string:the string you want to search for
  • pattern-to-find:the pattern used.
  • position:where to start.
  • occurrence:which occurrence of the pattern-to-find in the source-string you want to search for.
  • return-option:can be 0 or 1. If return-option is 0, it returns the first character of the occurrence; if return-option is 1, it returns the position of the character following the occurrence. 0 is the default.
  • match-parameter:for further customizing.
    • "i" in match-parameter can be used for caseinsensitive matching
    • "c" in match-parameter can be used for casesensitive matching
    • "n" in match-parameter allows the period to match the new line character
    • "m" in match-parameter allows for more than one line in source-string

The metacharacters list:

MetacharactersMeaning
\Specify the escape sequence
\dDigit character
\DNon-digit character
\wWord character
\WNon-word character
\sWhitespace character
\SNon-whitespace character
\AMatches only at the beginning of a string or before a newline character at the end of a string
\ZMatches only at the end of a string
^Matches the position at the start of the string.
$Matches the position at the end of the string.
*Matches the preceding character zero or more times.
+Matches the preceding character one or more times.
?Matches the preceding character zero or one time.
*?Matches the preceding pattern element 0 or more times
+?Matches the preceding pattern element 1 or more times
??Matches the preceding pattern element 0 or 1 time
{n}Matches a character exactly n times, where n is an integer.
{n,}Matches the preceding pattern element at least n times
{n,m}Matches a character at least n times and at most m times, where n and m are both integers.
.Matches any single character except null.
(pattern)A subexpression that matches the specified pattern.
x|yMatches x or y, where x and y are one or more characters. war|peace matches war or peace.
[abc]Matches any of the enclosed characters.
[a-z]Matches any character in the specified range.
[:alphanum:]matches alphanumeric characters 0-9, A-Z, and a-z.
[:alpha:]matches alphabetic characters A-Z and a-z.
[:blank:]matches space or tab.
[:digit:]matches digits 0-9.
[:graph:]matches non-blank characters.
[:lower:]matches lowercase alphabetic characters a-z.
[:print:]is similar to [:graph:] except [:print:] includes the space character.
[:punct:]matches punctuation characters .,"`, and so on.
[:space:]matches all whitespace characters.
[:upper:]matches all uppercase alphabetic characters A-Z.
[:xdigit:]matches characters permissible in a hexadecimal number 0-9, A-F, and a-f.
[..]Matches one collation element, like a multicharacter element.
[==]Specifies equivalence classes.
\nA backreference to an earlier capture, where n is a positive integer.

Example:

ExampleDescription
\nmatches the newline character
\\matches \
\(matches (
^Amatches if A is the first character in the string.
$Bmatches if B is the last character in the string.
f*dmatches flood, food, and so on.
fo+dmatches fod, food, and so on.
fo?dmatches fd and fod only.
fo{2}dmatches food.
fo{2,3}dmatches food and foood only.
[ab]bcmatches abc and bbc.
[a-c]bcmatches abc, bbc, and cbc.

SQL> SELECT REGEXP_INSTR('Mississippi', 'si', 1,2,0,'i') FROM dual;

REGEXP_INSTR('MISSISSIPPI','SI',1,2,0,'I')
------------------------------------------
                                         7

SQL> SELECT REGEXP_INSTR('This is a test, llll','l[[:alpha:]]{2}') AS result FROM dual;

    RESULT
----------
        17

SQL> SELECT REGEXP_INSTR('This is a test, llll lll lll lll','l[[:alpha:]]{2}', 1, 2) AS result FROM dual;

    RESULT
----------
        22

Query returns the position of the second occurrence that matches the letter o starting the search at position 10:


SELECT REGEXP_INSTR('this is a test, food lood, tood','o', 10, 2) AS result FROM dual;

    RESULT
----------
        19
Home »
Oracle »
String Functions » 

Related: