MySQL Regex Tester — REGEXP, RLIKE & REGEXP_LIKE Patterns

MySQL has two regex engines — and the version changes everything

Every MySQL regex question starts with the same fork: MySQL 8.0.0 and later use the International Components for Unicode (ICU) library, while MySQL 5.6 and 5.7 use the Henry Spencer library, a classic POSIX Extended Regular Expression implementation. The practical differences are not cosmetic. POSIX ERE has no \d, \w, or \s shorthand — only bracket classes like [[:digit:]] — no lazy quantifiers, and (until ICU arrived) no lookahead at all. A pattern written for one engine can be syntactically invalid or semantically different on the other, which is why this tester attaches a version notice to every construct whose behavior differs: \d earns a 'requires MySQL 8.0+' notice, and \b carries the backspace-versus-word-boundary warning because Spencer and ICU disagree about it.

Case sensitivity is the other version-spanning trap. MySQL string comparison follows the column's collation, and under a default *_ci collation REGEXP matches case-insensitively — unlike nearly every other language's regex engine. The tester applies the i flag automatically to mirror that default, and tells you it did. To opt into case sensitivity in MySQL 8, use REGEXP_LIKE(col, pattern, 'c'); in 5.x the honest answer is that REGEXP inherits the collation, so a _cs collation or UPPER() on both sides is the workaround.

POSIX classes are the portable subset — use them deliberately

The bracket classes — [[:alpha:]], [[:digit:]], [[:alnum:]], [[:upper:]], [[:lower:]], [[:space:]], [[:punct:]], [[:xdigit:]], [[:word:]] — work identically in 5.x and 8.0 and in every POSIX-flavored engine, which makes them the safest vocabulary for SQL you intend to keep. The tester expands each one to an explicit ASCII class covering exactly its POSIX members: [[:space:]] becomes the literal set of six whitespace characters POSIX defines, deliberately not JavaScript's broader \s, because silently widening a class is how a WHERE clause starts matching rows it should not. Negated classes like [[:^digit:]] translate too, as long as the POSIX class is the sole member of its brackets.

The generated WHERE-ready patterns you validate here transfer one-to-one to REGEXP, RLIKE, REGEXP_LIKE, REGEXP_SUBSTR, and REGEXP_REPLACE. The 8.0 function family matters more than people expect: REGEXP_SUBSTR(col, pattern) finally answers 'what matched' rather than just whether anything matched, REGEXP_REPLACE does what five versions of nested SUBSTRING_INDEX hacks used to, and REGEXP_INSTR returns match positions. In 5.x you get the boolean REGEXP/RLIKE and string functions only — a constraint worth knowing before you write the query.

SQL string literals escape your backslashes twice

The tester evaluates the raw pattern, but MySQL evaluates a pattern embedded in a SQL string literal — and by default MySQL treats backslash as an escape character inside string literals too. Writing REGEXP_LIKE(name, '\d+') sends the engine just d+ because the literal layer consumes one backslash; the pattern must be written '\\d+' (four backslashes in some client contexts) so the engine receives \d. This double-escaping is the single most common reason a pattern that works in a tester fails in production SQL, and every notice this tool shows refers to the engine-level pattern — the one after the string literal has done its work.

Two situational footnotes complete the picture. First, sql_mode=NO_BACKSLASH_ESCAPES disables that literal-layer escaping, so the same SQL text behaves differently on servers with different sql_mode settings. Second, patterns pasted straight from browser DevTools or shell scripts often arrive with a quoting history of their own — strip that layer in your head before pasting here, and let the tester reason about engine-level syntax only.

Performance: REGEXP scans, LIKE can sometimes seek

A regex predicate forces a full scan of every row in scope — there is no B-tree index that can satisfy REGEXP or RLIKE the way a left-anchored LIKE 'abc%' sometimes can. On a large table that is the difference between milliseconds and minutes, so the standard playbook is: narrow the candidate set with an indexed, sargable predicate first (a LEFT(name, 4) = 'INV-' or a generated column), then apply the regex to the small remainder. Testing the pattern here first is the cheap half of that workflow — the tester's match table shows you exactly which rows a pattern selects before a scan ever runs.

The 2-second execution budget in this tester doubles as a rehearsal for server-side behavior: catastrophic shapes like (a+)+ or (a|a)+ pinned against a non-matching string explode combinatorially in ICU just as they do in every backtracking engine, and on the database that is a query that never returns while it burns CPU. If the tester rejects your pattern for exceeding the budget, the pattern does not belong in a WHERE clause either — restructure it with possessive-free, bounded repetition before it reaches production.

Which MySQL version supports \d, \w, and \s in regex?

MySQL 8.0 and later, because the regex engine switched to ICU, which understands Perl-style shorthand. In MySQL 5.6 and 5.7 the Spencer library only accepts POSIX classes — write [[:digit:]] instead of \d or the query fails with an illegal operand error. The tester accepts both and attaches a version notice whenever you use the 8.0-only forms.

Why does my pattern work here but fail in the MySQL client?

Almost always double escaping. Inside a SQL string literal, MySQL consumes one backslash layer before the regex engine sees the pattern, so '\d+' arrives as 'd+'. Write '\\d+' in the SQL literal so the engine receives \d — or set sql_mode=NO_BACKSLASH_ESCAPES to disable the literal layer. The tester always evaluates the engine-level pattern, which is the form that must survive the escaping.

How do I make REGEXP case-sensitive in MySQL?

In MySQL 8, pass the match-type argument: REGEXP_LIKE(column, 'pattern', 'c') forces case-sensitive matching regardless of collation. In MySQL 5.x, REGEXP inherits the collation, so you need a case-sensitive or binary collation on the expression (for example CONVERT(col USING binary)) or compare UPPER() on both sides. The tester adds the i flag automatically to mirror the default *_ci behavior and says so in the notices.

What is the difference between REGEXP, RLIKE, and REGEXP_LIKE?

REGEXP and RLIKE are synonyms — both are boolean predicates usable in WHERE and return 1, 0, or NULL. REGEXP_LIKE() is the MySQL 8.0 function-family form with the same boolean result plus an optional match-type argument for case and multiline behavior. The 8.0 family adds REGEXP_SUBSTR (returns the matched text), REGEXP_INSTR (returns the position), and REGEXP_REPLACE — none of which exist in 5.x.

Can REGEXP use an index?

No. A regex predicate is evaluated row by row and forces a full scan of the rows in scope; B-tree indexes only help left-anchored LIKE patterns. The standard pattern is to pre-filter with a sargable predicate — an indexed prefix match or a generated column — and apply REGEXP to the narrowed set. Validating the pattern here first keeps the scan you do run as cheap as it can be.

Does MySQL regex match against numbers and dates?

REGEXP implicitly casts non-string operands to strings before matching, so a DECIMAL price or a DATE becomes its text representation first. That means locale and formatting affect what the pattern sees — a DATE renders as '2026-01-15', not as a timestamp. Match against explicit CAST or DATE_FORMAT output when the text shape matters, and test the exact rendered form here before shipping the query.