Regular expressions (regex) are one of the most powerful tools for working with text in Oracle AI Database. Whether you need to validate phone numbers, extract email addresses, clean messy data, or enforce complex business rules, Oracle’s built-in regex support makes it fast, efficient, and easy to implement directly in SQL.
Why Use Regular Expressions in Oracle?
- Centralize complex pattern-matching logic in the database instead of the application layer
- Enforce data quality and business rules with CHECK constraints
- Search, replace, and transform text with simple SQL functions
- Greatly simplify data validation, extraction, and cleansing tasks
Oracle SQL Regex Functions
Oracle provides five powerful functions/conditions for regular expressions:
| Function / Condition | Description |
|---|---|
| REGEXP_LIKE | Returns TRUE if the string matches the pattern (perfect for WHERE clauses and CHECK constraints) |
| REGEXP_COUNT | Counts how many times the pattern appears |
| REGEXP_INSTR | Returns the position where the pattern starts |
| REGEXP_SUBSTR | Extracts the matching substring |
| REGEXP_REPLACE | Replaces matching text with new content |
Basic Syntax and Options
REGEXP_LIKE(source_string, pattern [, match_parameter])Common match parameters:
'i'— case-insensitive'c'— case-sensitive (default)'n'— dot (.) matches newline'm'— multiline mode (^ and $ match start/end of lines)'x'— ignore whitespace in pattern
Practical Examples
Example 1: Enforce Phone Number Format with a CHECK Constraint
CREATE TABLE contacts (
last_name VARCHAR2(30),
phone VARCHAR2(30)
CONSTRAINT valid_phone
CHECK (REGEXP_LIKE(phone, '^\(\d{3}\) \d{3}-\d{4}$'))
);Example 2: Count Occurrences (Case-Insensitive)
SELECT REGEXP_COUNT('Albert Einstein', 'e', 1, 'i') AS count_e
FROM dual;Example 3: Extract Email Addresses
SELECT REGEXP_SUBSTR(email, '\w+@\w+(\.\w+)+') AS email_address
FROM employees;Example 4: Reposition Characters with Back References
SELECT
names,
REGEXP_REPLACE(names, '^(\S+)\s(\S+)\s(\S+)$', '\3, \1 \2') AS formatted_name
FROM famous_people;POSIX and PERL-Influenced Operators
Oracle supports the full set of POSIX operators plus useful PERL-style shortcuts:
\d— digit\w— word character (alphanumeric + underscore)\s— whitespace\A,\Z,\z— string anchors- Nongreedy quantifiers (
+?,*?, etc.)
Best Practices
- Use regex in CHECK constraints to enforce data quality at the database level
- Prefer regex over complex string functions when pattern matching is involved
- Test thoroughly — regex can be tricky with edge cases
- Use the
'x'flag to make complex patterns more readable - Combine with other SQL features (e.g., REGEXP_REPLACE inside UPDATE statements)
Conclusion
Regular expressions in Oracle AI Database give you enterprise-grade text processing power directly in SQL. From simple validation to complex data transformation, regex lets you solve real-world text problems efficiently without leaving the database.
Whether you’re cleaning data, enforcing business rules, or building sophisticated search features, mastering Oracle regex will make you a much more effective developer.