Oracle REGEXP_INSTR Function

REGEXP_INSTR returns the position in a string where a regular expression matches, or 0 if it does not match. It is the pattern-matching version of INSTR: instead of searching for a fixed substring, you can search for the first digit, the last group of four digits, or the second space, and get either the start of the match or the position just after it.

Code for This Guide

The main example is in the examples/character-functions folder of the Oracle Database 26ai code repository on GitHub, with its output. It queries NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

It comes from Oracle Database 26ai SQL and PL/SQL Book.

Syntax

Syntax:

regexp_instr(source_char, pattern
             [, position [, occurrence [, return_option [, match_param [, subexpr]]]]])

Find Positions in a Phone Number

This query finds four positions in a customer's phone number: the first digit, the second space, the start of the final group of four digits, and the position right after it.

Example:

select phone,
       regexp_instr(phone, '\d')              as first_digit,
       regexp_instr(phone, ' ', 1, 2)         as second_space,
       regexp_instr(phone, '\d{4}$')          as last_group,
       regexp_instr(phone, '\d{4}$', 1, 1, 1) as after_last_group
from   customers
where  customer_id = 1;

Output:

PHONE                  FIRST_DIGIT    SECOND_SPACE    LAST_GROUP    AFTER_LAST_GROUP
___________________ ______________ _______________ _____________ ___________________
+971 22 414 1147                 2               8            13                  17

The first digit is at position 2, after the plus sign. With occurrence 2, ' ' finds the second space at 8. '\d{4}$' matches four digits at the end of the string, starting at 13; return_option 1 gives 17, one past the end of the match, which is handy as the start position for a following SUBSTR.

Find the Position of a Group

The subexpr argument reports where a particular group of the pattern matched. Here the pattern has two groups, the country code and the area code, and subexpr 2 returns where the area code starts. REGEXP_SUBSTR with the same arguments returns the area code itself.

Example:

-- the position of the second group of the pattern: the area code after the country code
select phone,
       regexp_instr(phone, '^\+(\d+) (\d+)', 1, 1, 0, null, 2) as area_code_at,
       regexp_substr(phone, '^\+(\d+) (\d+)', 1, 1, null, 2)   as area_code
from   customers
where  customer_id in (1, 2, 3);

Output:

PHONE                  AREA_CODE_AT AREA_CODE
___________________ _______________ ____________
+971 22 414 1147                  6 22
+91 82 899 2343                   5 82
+44 20 559 4746                   5 20

To pass subexpr you must pass all the arguments before it; NULL for match_param means no flags.

Things to Know

• REGEXP_INSTR returns 0, not NULL, when there is no match, so test the result with > 0.
• Positions count characters, not bytes, in multibyte character sets.
• A NULL source string or pattern returns NULL.
• Inside square brackets, use [[:digit:]] rather than \d.

Related Guides

• Oracle REGEXP_LIKE Function

Conclusion

REGEXP_INSTR returns where a regular expression matches in a string: the start of the match by default, or the position after it with return_option 1. Choose the match with occurrence, start later with position, and report a single group of the pattern with subexpr.