Regular Expressions

Oracle's regular expression (regex) functions let you match, search, replace, and extract substrings using pattern syntax — far more powerful than the basic LIKE operator. They're the right tool for validating emails, extracting phone numbers from free text, finding rows that match complex patterns, or cleaning messy data.

Oracle's regex flavour is POSIX Extended Regular Expressions (with some Perl-compatible extras since 10gR2). If you know regex from Python, JavaScript, or grep, most patterns translate directly.

The Five REGEXP Functions

Oracle provides five regex functions. Each takes a string and a pattern, plus optional modifiers:

All five accept a final match parameter that controls case sensitivity, multiline behaviour, and more (covered below).

REGEXP_LIKE — Pattern Matching in WHERE

REGEXP_LIKE is the regex equivalent of LIKE. It returns true when the source string matches the pattern.

-- Find employees whose first_name starts with 'A' or 'a'
SELECT first_name, last_name
FROM   employees
WHERE  REGEXP_LIKE(first_name, '^[Aa]');

Result:

^[Aa] reads as "start of string, then either A or a".

Compare with the basic LIKE equivalent:

-- LIKE works for simple patterns but can't express "A or a" without ORs
WHERE first_name LIKE 'A%' OR first_name LIKE 'a%'

Regex shines once patterns become non-trivial:

-- Employees whose phone number is in (123) 456-7890 format:
SELECT first_name, phone_number
FROM   employees
WHERE  REGEXP_LIKE(phone_number, '^\([0-9]{3}\) [0-9]{3}-[0-9]{4}$');

That single pattern would take 20 LIKE clauses.

REGEXP_SUBSTR — Extract a Match

REGEXP_SUBSTR returns the substring that matched the pattern, or NULL if there was no match.

-- Extract the area code from a phone number
SELECT REGEXP_SUBSTR('(415) 555-1234', '\(([0-9]{3})\)') AS area_code
FROM   dual;

Result: (415)

Without parentheses around the digits, that returns the whole match including the literal ( and ). To return only the captured group (the digits inside), add the subexpression parameter:

SELECT REGEXP_SUBSTR('(415) 555-1234',
                     '\(([0-9]{3})\)',
                     1, 1, NULL, 1) AS area_code
FROM   dual;

Result: 415

The 6 arguments are: (source, pattern, position, occurrence, match_param, subexpression). The last one — subexpression — picks which (...) capture group to return (1 for the first, 2 for the second, etc.).

Common Extraction Patterns

-- Extract everything before the @ in an email
SELECT REGEXP_SUBSTR('alice@company.com', '^[^@]+') FROM dual;
-- Result: alice

-- Extract digits only from a noisy string
SELECT REGEXP_SUBSTR('Order #12345 (priority)', '\d+') FROM dual;
-- Result: 12345

-- Extract the 3rd word
SELECT REGEXP_SUBSTR('The quick brown fox', '\w+', 1, 3) FROM dual;
-- Result: brown

-- Extract every word as a separate row (combine with CONNECT BY)
SELECT REGEXP_SUBSTR('The quick brown fox', '\w+', 1, LEVEL) AS word
FROM   dual
CONNECT BY REGEXP_SUBSTR('The quick brown fox', '\w+', 1, LEVEL) IS NOT NULL;

REGEXP_REPLACE — Search and Replace

REGEXP_REPLACE replaces every match of the pattern with a replacement string.

-- Strip all non-digit characters from a phone number
SELECT REGEXP_REPLACE('(415) 555-1234', '[^0-9]', '') AS cleaned
FROM   dual;

Result: 4155551234

Use \1, \2, etc. in the replacement to refer to captured groups:

-- Reformat a date from MM/DD/YYYY to YYYY-MM-DD
SELECT REGEXP_REPLACE('03/15/2024',
                      '^([0-9]{2})/([0-9]{2})/([0-9]{4})$',
                      '\3-\1-\2') AS iso_date
FROM   dual;

Result: 2024-03-15

Cleaning Whitespace

-- Collapse multiple spaces into one
SELECT REGEXP_REPLACE('  hello   world  ', '\s+', ' ') FROM dual;
-- Result: ' hello world '

-- Trim leading and trailing whitespace
SELECT REGEXP_REPLACE('  hello world  ', '^\s+|\s+$', '') FROM dual;
-- Result: 'hello world'

For trimming specifically, the built-in TRIM is faster and clearer than a regex.

REGEXP_INSTR — Position of a Match

Returns the 1-based character position where the pattern matches, or 0 if there's no match.

-- Find where the @ symbol starts in an email
SELECT REGEXP_INSTR('alice@company.com', '@') FROM dual;
-- Result: 6

-- Find the position AFTER the match using return_option = 1:
SELECT REGEXP_INSTR('alice@company.com', '@', 1, 1, 1) FROM dual;
-- Result: 7 (position right after @)

Useful for splitting strings or trimming based on a pattern.

REGEXP_COUNT — How Many Matches

-- Count vowels in a string
SELECT REGEXP_COUNT('Database', '[aeiouAEIOU]') FROM dual;
-- Result: 4 (a, a, a, e)

-- Count words
SELECT REGEXP_COUNT('The quick brown fox', '\w+') FROM dual;
-- Result: 4

Pattern Syntax Cheat Sheet

Character Classes

Quantifiers

Anchors

Grouping and Alternation

POSIX Character Classes

Oracle's POSIX flavour supports named classes inside [ ]:

Note the double brackets when using them: [[:digit:]] matches a digit, just like \d.

-- Match if the string is entirely letters
WHERE REGEXP_LIKE(name, '^[[:alpha:]]+$')

Match Parameter (the Final Flag)

The optional match parameter is a string of flags that modify behaviour:

-- Case-insensitive search
WHERE REGEXP_LIKE(comments, 'error', 'i');

-- Combine flags: ignore case AND let . match newlines
WHERE REGEXP_LIKE(log_text, 'failed.+retry', 'in');

Real-world Patterns

Email Validation (Pragmatic, Not Perfect)

WHERE REGEXP_LIKE(email,
  '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')

A "fully RFC-5322 compliant" email regex is hundreds of characters and not worth the complexity. The pattern above catches 99% of real-world email formatting bugs.

US Phone Number (Multiple Formats)

-- Accept (415) 555-1234, 415-555-1234, 415.555.1234, 4155551234
WHERE REGEXP_LIKE(phone,
  '^\(?[0-9]{3}\)?[ .-]?[0-9]{3}[ .-]?[0-9]{4}$')

US ZIP Code

-- 5-digit or ZIP+4 (12345 or 12345-6789)
WHERE REGEXP_LIKE(zipcode, '^[0-9]{5}(-[0-9]{4})?$')

Strong Password (8+ chars, mix of types)

WHERE REGEXP_LIKE(pwd, '^(?=.*[a-z])(?=.*[A-Z])(?=.*\d).{8,}$')

This uses lookaheads (?=...) which Oracle supports since 10gR2.

Extract URL Components

SELECT
  REGEXP_SUBSTR(url, '^([^:]+)',        1, 1, NULL, 1) AS protocol,
  REGEXP_SUBSTR(url, '://([^/]+)',      1, 1, NULL, 1) AS host,
  REGEXP_SUBSTR(url, '(/[^?]*)',        1, 1, NULL, 1) AS path
FROM   urls;

Performance Considerations

Regex functions are CPU-expensive compared to LIKE or simple string equality. Two principles:

1. Use LIKE when LIKE is enough. LIKE 'A%' is much faster than REGEXP_LIKE(col, '^A').
2. Add a B-tree index only on equality columns. Plain text indexes cannot accelerate regex searches.

Function-Based Indexes

If you frequently search on a regex-transformed version of a column (e.g., a lowercased name), a function-based index can help — but the function must match exactly:

-- Index on lowercased name:
CREATE INDEX emp_lname_lower_idx ON employees (LOWER(last_name));

-- This query uses the index:
WHERE LOWER(last_name) = 'smith';

-- This does NOT — REGEXP_LIKE can't use a B-tree index on LOWER(last_name):
WHERE REGEXP_LIKE(last_name, 'smith', 'i');

For true full-text searching, Oracle Text (the CONTEXT index type) is purpose-built and orders of magnitude faster than REGEXP_LIKE on large tables. Use Oracle Text when you need substring or proximity searches across many gigabytes.

Avoiding Function Calls in WHERE

-- Slow: regex runs on every row of the table
SELECT * FROM customer_messages
WHERE  REGEXP_LIKE(body, 'urgent.*follow.up', 'i');

-- Faster: narrow the candidate set first with an indexed condition
SELECT * FROM customer_messages
WHERE  status = 'OPEN'
  AND  created_at > SYSDATE - 7
  AND  REGEXP_LIKE(body, 'urgent.*follow.up', 'i');

The first condition can use an index on status or created_at; the regex only runs on the small candidate set that survives.

Common Patterns Across Languages

Common Errors

Interview Corner

WHERE phone LIKE '%(415)%'                              -- index-friendly
  AND REGEXP_LIKE(phone, '^\(415\)\s\d{3}-\d{4}$')      -- exact format

SELECT REGEXP_REPLACE('+1 (415) 555-1234', '[^0-9]', '') FROM dual;
-- Result: 14155551234

SELECT REGEXP_REPLACE('415.555.1234',     '[^0-9]', '') FROM dual;
-- Result: 4155551234

SELECT REGEXP_REPLACE('(415) 555 1234',    '[^0-9]', '') FROM dual;
-- Result: 4155551234

NVL(REGEXP_REPLACE(phone, '[^0-9]', ''), '')

WITH digits_only AS (
  SELECT REGEXP_REPLACE('+1 (415) 555-1234', '[^0-9]', '') AS d FROM dual
)
SELECT CASE
         WHEN LENGTH(d) = 11 AND SUBSTR(d, 1, 1) = '1' THEN SUBSTR(d, 2)
         ELSE d
       END AS national_number
FROM digits_only;
-- Result: 4155551234

Related Topics

• Filtering & Conditions — LIKE and IN for simpler pattern matches
• Basic SQL — SUBSTR, INSTR, REPLACE for non-regex string ops
• DML Commands — UPDATE with REGEXP_REPLACE for bulk data cleaning
• Performance — function-based indexes, Oracle Text
• Indexes — why regex can't use B-tree indexes and what to do about it