SQL, Unicode, and Performance Notes

Regex in databases

Most modern databases support regex matching:

PostgreSQL: ~ (case-sensitive), ~* (case-insensitive), !~/!~* (negated). Uses POSIX ERE.
MySQL/MariaDB: REGEXP or RLIKE. Limited POSIX ERE.
SQLite: REGEXP requires loading an extension or using a user-defined function.
Snowflake: RLIKE, REGEXP_LIKE, plus REGEXP_SUBSTR, REGEXP_REPLACE.

Common helper functions across DBs: regexp_replace, regexp_substr, regexp_count.

Unicode in regex — the gotchas

The biggest source of cross-language regex bugs is Unicode handling:

Python re: Unicode-aware by default for \d, \w, \s. Use re.ASCII to revert.
JavaScript: Need /u flag for proper Unicode. \w still ASCII-only even with /u — use \p{L} for Unicode letters.
RE2/Go: ASCII-only by default. Use \p{Nd}, \pL for Unicode categories.
PCRE: ASCII by default; (*UCP) or /u for Unicode-aware.

If your data is Unicode, always test the regex against actual Unicode input. ASCII tests don't reveal these bugs.

Performance — the universal rules

Compile patterns you reuse. One-time cost amortized over many matches.
Anchor when you can. ^pattern stops the engine from trying every position.
Prefer character classes over alternation. [abc] beats a|b|c.
Avoid catastrophic backtracking. Patterns like (a+)+, (a|a)+, (.*)* are exponential. Track 8 covers ReDoS.
For huge files: stream, don't load. Most regex APIs work on iterators or generators.

The takeaway

Regex is dialect-aware. Same idea, slightly different surface in every environment. The Track 1 mental model — "shape language, engine walks the input" — carries everywhere. The 80% portable subset is your home; the rest is dialect lookup when you need it.

Code

Database regex examples·sql

-- PostgreSQL
SELECT * FROM users WHERE email ~ '^[\w.+-]+@example\.com$';
SELECT regexp_replace(name, '\s+', '_', 'g') FROM users;

-- MySQL
SELECT * FROM logs WHERE message REGEXP 'ERROR|WARN';
SELECT REGEXP_SUBSTR(url, '/api/v[0-9]+/[^/]+') FROM requests;

-- Snowflake
SELECT * FROM events WHERE payload RLIKE '"event_type":"(login|signup)"';
SELECT REGEXP_REPLACE(text, '[^\x20-\x7E]', '', 1, 0, 'g') FROM messages;

-- SQLite (needs REGEXP function loaded)
SELECT * FROM logs WHERE level REGEXP '^(ERROR|FATAL)$';

SQL, Unicode, and Performance Notes

Regex in databases

Unicode in regex — the gotchas

Performance — the universal rules

The takeaway

Code

External links

Exercise

Progress

Comments 0