Regular Expressions
Domain 13 tests Oracle regular expression functions: REGEXP_LIKE, REGEXP_SUBSTR, REGEXP_INSTR, REGEXP_REPLACE, and REGEXP_COUNT. You must read patterns, use match parameters, and predict what is returned or replaced.
Core rules
- REGEXP_LIKE is a predicate for WHERE clauses and returns TRUE or FALSE. REGEXP_SUBSTR extracts the matching substring. REGEXP_INSTR returns the position of a match. REGEXP_REPLACE substitutes matches. REGEXP_COUNT counts matches.
- Patterns use POSIX-style elements: character classes such as [0-9] and [A-Z], quantifiers such as * + ? and {n}, anchors ^ and $, and alternation with |.
- Match parameters include i for case-insensitive matching, c for case-sensitive matching, n for dot matching newline, m for multiline anchors, and x for ignoring whitespace in the pattern.
- Backreferences let you reuse captured groups in replacement strings. Parentheses create capture groups.
- Regular expression functions generally return NULL when given NULL input.
Worked example
SELECT REGEXP_SUBSTR('EMP-1234-XY','[0-9]{4}') AS digits, REGEXP_REPLACE('a1b2','[0-9]','') AS letters FROM DUAL;
REGEXP_SUBSTR scans the string for exactly four digits and returns 1234. REGEXP_REPLACE replaces every digit with an empty string, leaving ab. DUAL supplies the single row needed for the expression.
Traps
REGEXP_LIKE belongs in a predicate, not in the SELECT list as a Boolean output. Escaping backslashes in SQL strings can require care. A greedy quantifier such as .* may consume more than intended. Patterns are case-sensitive by default unless the i parameter is used. REGEXP_INSTR returns 0 when no match is found.
Key points
- REGEXP_LIKE filters rows.
- REGEXP_SUBSTR extracts a matching substring.
- REGEXP_REPLACE substitutes matches.
- Match parameter i enables case-insensitive matching.
- REGEXP_INSTR returns 0 when no match exists.