Pattern matching with regular expressions

Oracle 10g introduced a powerful set of regular expression functions. They're far more flexible than LIKE and are essential for complex text parsing, validation, and extraction.

Recipe #1 - REGEXP_LIKE for pattern matching in WHERE clauses

Find all employees whose names start with a vowel:

SELECT ename FROM emp
WHERE REGEXP_LIKE(ename, '^[AEIOU]', 'i');

Validate that a string looks like an email address:

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

Recipe #2 - REGEXP_SUBSTR to extract parts of a string

Extract the domain from an email address:

SELECT email,
       REGEXP_SUBSTR(email, '@(.+)$', 1, 1, NULL, 1) AS domain
FROM users;

Extract the nth word from a string:

SELECT REGEXP_SUBSTR('Oracle Database 19c Enterprise Edition',
                     '[^ ]+', 1, 3) AS third_word
FROM DUAL;

Result: 19c

Recipe #3 - REGEXP_REPLACE for advanced find-and-replace

Remove all non-numeric characters from a phone number:

SELECT REGEXP_REPLACE('+41 (79) 123-45-67', '[^0-9]', '') AS clean_number
FROM DUAL;

Result: 41791234567

Convert camelCase to snake_case:

SELECT REGEXP_REPLACE('getEmployeeSalary', '([A-Z])', '_\1')
FROM DUAL;

Recipe #4 - REGEXP_COUNT to count occurrences

Count how many words are in a string:

SELECT REGEXP_COUNT('The quick brown fox', '\S+') AS word_count
FROM DUAL;

Count vowels:

SELECT REGEXP_COUNT('Oracle Database', '[aeiou]', 1, 'i') AS vowel_count
FROM DUAL;

Recipe #5 - REGEXP_INSTR to find pattern positions

Find the position of the first digit in a string:

SELECT REGEXP_INSTR('Room 42B on Floor 3', '[0-9]') AS first_digit_pos
FROM DUAL;

Regular expressions are computationally more expensive than simple LIKE comparisons, so use them when you genuinely need pattern matching power. For simple prefix/suffix checks, LIKE is still faster.