Pattern Matching

Definition

Beyond LIKE's simple %/_ wildcards, most SQL dialects also support regular expressions for genuinely complex pattern matching — a much more expressive (and more complex) matching language.

Running example — a products(id, name, category, price, stock) table, where stock can be NULL (not yet counted):

sql
-- MySQL / MariaDB
SELECT name FROM products WHERE name REGEXP '^Pro[A-Z]';

-- PostgreSQL
SELECT name FROM products WHERE name ~ '^Pro[A-Z]';

Regular expressions can express patterns LIKE fundamentally cannot: "starts with 'Pro' followed by an uppercase letter," "contains exactly one digit," "matches one of several alternative word forms" — anything requiring real pattern logic beyond simple substring/wildcard matching.

Edge Cases and Pitfalls

• Regular expression SYNTAX and the function/operator NAME to invoke it (REGEXP, RLIKE, ~, SIMILAR TO) both vary significantly by dialect — this is one of the least portable areas of SQL, more so even than most other dialect differences covered in this course.
• Regular expression matching is typically much more computationally expensive than a simple LIKE pattern, especially a complex regex run over a large table with no supporting index — reach for LIKE first when a simple wildcard genuinely suffices.
• Because regex patterns use characters like ., *, [, ], ^, $ with special meaning, matching those characters LITERALLY requires escaping them — a common source of "why doesn't my pattern match" confusion for anyone used to LIKE's much simpler two-wildcard system.

Key Takeaways

• Full regular expressions (via REGEXP/RLIKE/~ depending on dialect) handle pattern-matching needs beyond what LIKE's simple wildcards can express.
• Regex syntax and invocation keywords are notably dialect-fragmented — expect real portability friction.
• Prefer simple LIKE patterns when they suffice; reach for regex only when genuinely needed, given the performance and complexity cost.