When LIKE is not enough. Write a real pattern and watch it match — character by character — against every row, with the equivalent LIKE shown alongside so you can see exactly what regex buys you.
Regular-expression matching is often provided by a database-specific operator or function. Pattern syntax, flags, capture behavior, and available functions are not portable. In SQLite, the REGEXP operator calls an application-defined regexp() function; SQLite does not provide that function by default. An editor or driver may register one, so confirm what the actual application supports.
For exact text or simple wildcards, use = or LIKE instead of a regular expression. In a LIKE pattern, % matches any sequence and _ matches one character; escape those characters when they should be literal.
A pattern should express the actual rule, not a vague approximation. For example, a regex that accepts a numeric identifier should specify whether signs, spaces, and decimals are allowed. Test valid and invalid boundary cases. For user-provided patterns, consider runtime limits: pathological expressions can consume significant CPU in some engines or libraries.
Regex matching often cannot use an ordinary B-tree index, particularly when the pattern begins with arbitrary text. Some databases offer specialized indexes or optimized pattern operators; inspect the query plan and benchmark.
If a pattern defines a required format, validate it at input boundaries and, where the database supports it, add an appropriate constraint. Do not assume a regex check proves that a value is a valid email address, URL, or other complex semantic object. See SQL data types and query plans.
A pattern can check a useful local format, such as digits in an identifier, but it cannot reliably prove that an email address can receive mail or that a URL is safe to fetch. Those requirements need parsing, verification, and security rules beyond character matching. Anchor a validation pattern to the whole input when partial matches are not acceptable, and test Unicode and newline behavior in the selected engine. For user-entered search text, escape regex metacharacters if the user expects literal matching.
A search pattern may intentionally match a substring, but an input validator often needs to match the entire value. Anchoring a pattern and handling newline behavior are dialect-specific details worth testing. Also define normalization rules: case folding and Unicode character classes may differ between the application and database. If the application validates one way and the database filters another, users can see inconsistent results. Keep a small shared test set of accepted and rejected inputs for both layers.
About to run: switch Column to code. Before it does — what happens to the readout?
Committing to an answer first is the point — the reveal runs the experiment on the visualisation above and reads the real value back, so nothing here is scripted.
Say the answer out loud before you reveal it — recalling it is what makes it stick, and rereading it is not.
What does this module say about “SQL does not define one regex dialect”?
What does this module say about “Write the narrowest useful pattern”?
What does this module say about “Prefer constraints for data integrity”?
Match patterns that LIKE cannot express — character classes, quantifiers and anchors — applied directly inside a SQL WHERE clause.