Oracle Regular Expressions for Data Validation and Parsing

Powerful Pattern Matching in SQL

Oracle’s regular expression functions, available since 10g, provide POSIX-compliant pattern matching directly in SQL and PL/SQL. For APEX developers, regex is invaluable for input validation, data cleaning, and parsing semi-structured text without PL/SQL loops or custom functions.

The Four Core Functions

REGEXP_LIKE: Tests whether a string matches a pattern. Use in WHERE clauses and CHECK constraints:

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

-- Check constraint for phone numbers
ALTER TABLE contacts ADD CONSTRAINT chk_phone
    CHECK (REGEXP_LIKE(phone, '^\+?[0-9]{10,15}$'));

REGEXP_SUBSTR: Extracts the substring matching a pattern:

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

-- Extract first number from a string
SELECT REGEXP_SUBSTR('Order #12345 placed', '[0-9]+') AS order_num FROM DUAL;

REGEXP_REPLACE: Replaces matches with a substitution string:

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

-- Mask credit card numbers
SELECT REGEXP_REPLACE(card_number, '([0-9]{4})([0-9]{8})([0-9]{4})',
                      '
********') AS masked FROM payments;

REGEXP_INSTR: Returns the position of a match:

-- Find position of first digit
SELECT REGEXP_INSTR('Room A-123', '[0-9]') AS first_digit_pos FROM DUAL;

APEX Validation With Regex

In APEX, create a validation of type “Regular Expression” on any text item. APEX runs the regex check server-side and displays inline error messages. Common patterns:

-- US ZIP code: 12345 or 12345-6789
^[0-9]{5}(-[0-9]{4})?$

-- Strong password: 8+ chars, uppercase, lowercase, digit, special
^(?=.*[a-z])(?=.*[A-Z])(?=.*[0-9])(?=.*[!@#$%]).{8,}$

-- IP address
^((25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.){3}(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)$

Performance Warning

Regular expression functions are significantly slower than LIKE, INSTR, and SUBSTR for simple patterns. Use regex only when simple string functions cannot express the pattern. Never use REGEXP_LIKE in a WHERE clause on a large table without an index that can reduce the candidate rows first, or you will force a full table scan with expensive regex evaluation on every row.