Oracle Regular Expressions: REGEXP Functions in SQL and PL/SQL

Oracle’s five REGEXP functions

• REGEXP_LIKE(str, pattern) — boolean match test (for WHERE clauses)
• REGEXP_INSTR(str, pattern) — position of match
• REGEXP_SUBSTR(str, pattern) — extract matching substring
• REGEXP_REPLACE(str, pattern, replacement) — replace matches
• REGEXP_COUNT(str, pattern) — count matches

REGEXP_LIKE: validation in WHERE clauses

-- Valid email format:
WHERE REGEXP_LIKE(email, '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$')

-- Indian mobile numbers:
WHERE REGEXP_LIKE(phone, '^[6-9][0-9]{9}$')

-- Alphanumeric only (no special chars):
WHERE REGEXP_LIKE(code, '^[A-Za-z0-9]+$')

REGEXP_SUBSTR: data extraction

Extract specific parts of structured strings:

-- Extract domain from email:
SELECT REGEXP_SUBSTR(email, '@(.+)', 1, 1, NULL, 1) AS domain FROM contacts;

-- Extract first word:
SELECT REGEXP_SUBSTR(description, '\w+') AS first_word FROM items;

-- Extract all numbers from a mixed string:
SELECT REGEXP_SUBSTR('Invoice INV-2026-001234 amount 5,432.50', '[0-9,]+', 1, 2) AS amount
FROM DUAL; -- returns '5,432.50'

REGEXP_REPLACE: transformation

-- Remove all non-numeric characters from phone:
SELECT REGEXP_REPLACE(phone_raw, '[^0-9]', '') AS clean_phone FROM contacts;

-- Normalise whitespace:
SELECT REGEXP_REPLACE(address, '\s+', ' ') AS clean_address FROM locations;

-- Mask middle digits of a card number:
SELECT REGEXP_REPLACE(card_no, '(\d{4})(\d{8})(\d{4})', '
XXXXXXXX') AS masked
FROM DUAL;

REGEXP_COUNT: finding frequency

-- Count comma-separated values in a field:
SELECT item_list, REGEXP_COUNT(item_list, ',') + 1 AS item_count FROM orders;

-- Count capital letters:
SELECT REGEXP_COUNT(company_name, '[A-Z]') AS cap_count FROM companies;

Performance considerations

Regular expressions are more expensive than LIKE for simple patterns. Use LIKE for straightforward prefix/suffix matching; reserve REGEXP for patterns that genuinely require it. Consider function-based indexes on frequently-filtered regex patterns.

Case-insensitive matching

Pass 'i' as the match parameter:

WHERE REGEXP_LIKE(description, 'oracle', 'i')  -- matches Oracle, ORACLE, oracle