Oracle REGEXP_LIKE Function

REGEXP_LIKE tests whether a string matches a regular expression. It is the pattern-matching counterpart of LIKE: where LIKE only knows % and _, REGEXP_LIKE understands character classes, alternatives, repetition, and anchors, so one condition can find names that start with one of several prefixes, values that contain white space, or input that is not a valid e-mail address. Strictly it is a condition rather than a function, so it goes in WHERE, CASE, or a CHECK constraint, not in the select list on its own.

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_like(source_char, pattern [, match_param])

Without a match parameter, case sensitivity follows the session's NLS_SORT setting, which is case-sensitive by default. The condition is true when the pattern matches anywhere in the string; anchor it with ^ and $ to match the whole string.

Find Names by Pattern

This query finds customers whose last name begins with van or O' in any case, or contains white space.

Example:

select customer_id, first_name, last_name
from   customers
where  regexp_like(last_name, '^(van|o'')', 'i')
   or  regexp_like(last_name, '\s')
order  by customer_id;

Output:

   CUSTOMER_ID FIRST_NAME    LAST_NAME
______________ _____________ _______________
            18 Mohammed      van der Berg
            82 Sakura        Al Mansoori
           100 Zoë           Al Mansoori
           113 Zoë           van der Berg
           118 Charlotte     van der Berg
           119 Amara         van der Berg

6 rows selected.

'^(van|o'')' combines an anchor, a group, and an alternative; the apostrophe is doubled because the pattern is a text literal. '\s' matches any white space character.

Validate Data

REGEXP_LIKE inside CASE counts the values that pass a check. The first query checks e-mail addresses; the second checks phone numbers and shows the most common pattern mistake in Oracle; the third shows the effect of the i flag.

Example:

-- customers whose e-mail address looks like name@domain.tld
select count(*) as customers,
       count(case when regexp_like(email, '^[^@ ]+@[^@ ]+\.[a-z]{2,}$', 'i') then 1 end)
         as valid_emails
from   customers;

-- inside brackets, \d is not a digit class: use [:digit:] there
select count(case when regexp_like(phone, '^\+\d[\d ]+$') then 1 end)        as with_backslash_d,
       count(case when regexp_like(phone, '^\+\d[[:digit:] ]+$') then 1 end) as with_posix_class
from   customers;

-- case matters unless you pass 'i'
select count(case when regexp_like(last_name, '^VAN')      then 1 end) as case_sensitive,
       count(case when regexp_like(last_name, '^VAN', 'i') then 1 end) as case_insensitive
from   customers;

Output:

   CUSTOMERS    VALID_EMAILS
____________ _______________
         120             120

   WITH_BACKSLASH_D    WITH_POSIX_CLASS
___________________ ___________________
                  0                 120

   CASE_SENSITIVE    CASE_INSENSITIVE
_________________ ___________________
                0                   4

Inside square brackets, Oracle does not treat \d as the digit class: [\d ] means a backslash, the letter d, or a space, so no phone number matches. Use the POSIX class [[:digit:]] inside brackets, or \d outside them. Without the i flag, ^VAN matches none of the lowercase van der Berg names.

Things to Know

• REGEXP_LIKE can enforce a format in a CHECK constraint, for example check (regexp_like(code, '^[A-Z]{3}$')).
• Oracle's regular expressions have no lookahead or lookbehind; use groups and back references instead.
• A NULL string or pattern makes the condition unknown, so the row is not returned.
• For a fixed prefix or substring, LIKE is simpler and can use an index; use REGEXP_LIKE when the pattern needs more.

Related Guides

• How NULL Works in Oracle Conditions

Conclusion

REGEXP_LIKE is true when a string matches a regular expression. Anchor patterns with ^ and $, pass i for case-insensitive matching, use [[:digit:]] rather than \d inside brackets, and use it in WHERE, CASE, or CHECK constraints to find and validate data by pattern.