Convert complex regular expressions into optimized SQL LIKE patterns. Stop writing full regex queries when a simple wildcard will do.
Use the pattern you already have from code, validation rules, or app search logic.
The output adapts to PostgreSQL, MySQL, or SQLite syntax automatically.
Replace column_name and paste the clause into your query.
^start anchor → prefix constraint$end anchor → suffix constraint.*any-chars wildcard → maps to % in SQLOptimized to standard SQL wildcard pattern
Real-world patterns — click any card to load it into the tool above
Which regex tokens map to SQL wildcards — and which require full regex operators
| Regex Token | What It Means | SQL Equivalent | Index-Friendly |
|---|---|---|---|
^abc | Starts with "abc" | LIKE 'abc%' | Yes |
abc$ | Ends with "abc" | LIKE '%abc' | Partial |
^abc$ | Exact match "abc" (fully anchored) | LIKE 'abc' | Yes |
.* | Any characters, zero or more | % wildcard | — |
. | Any single character | _ wildcard | — |
\. | Literal dot character | . (literal) | — |
(a|b) | Alternation — match "a" or "b" | REGEXP required | — |
[a-z] | Character class | REGEXP required | — |
a+ | One or more repetitions | REGEXP required | — |
a? | Zero or one (optional character) | REGEXP required | — |
a{3} | Exactly 3 repetitions | REGEXP required | — |
SQLite GLOB uses * and ? instead of % and _. Select SQLite above to see GLOB output.
The exact SQL operators for each database — quick cheat sheet
LIKEILIKE~~*!~ / !~*ILIKE is PostgreSQL-specific. The ~ operator uses POSIX extended regular expressions. Prefix LIKE with an index is very efficient.
LIKEREGEXPRLIKEREGEXP BINARYNOT REGEXPREGEXP is case-insensitive by default in MySQL 8. Use REGEXP BINARY for byte-level case-sensitive matching.
LIKEGLOBREGEXPNOT LIKENOT GLOBGLOB uses * (any chars) and ? (single char) instead of % and _. REGEXP requires sqlite3_create_function() or a loadable extension.
Why switching from REGEXP to LIKE can make a query 100× faster
LIKE 'abc%'Prefix LIKEThe B-tree index finds the first entry starting with "abc" and reads only the contiguous matching leaf pages — skipping the rest entirely.
LIKE '%abc'Suffix LIKEThere is no fixed starting point in the index, so the database engine must evaluate every single row.
Workaround: Store REVERSE(col) in a generated column and run a prefix LIKE on that.
LIKE '%abc%'Contains LIKEThe wildcard on both sides forces a complete table scan. For large datasets use a full-text index (PostgreSQL GIN with pg_trgm, MySQL FULLTEXT).
~ 'abc.*'PostgreSQL ~Standard B-tree indexes are bypassed. A pg_trgm GIN index can accelerate specific POSIX patterns (e.g. trigram-based prefix searches).
REGEXP 'abc.*'MySQL REGEXPMySQL applies REGEXP as a post-filter after fetching rows — a B-tree index provides no seek benefit here.
Common gotchas that produce silent bugs — even for experienced developers
WHERE name LIKE '%100%'WHERE name LIKE '%100\%%' ESCAPE '\\'If your data could contain literal % or _, they must be escaped with the ESCAPE clause. Without escaping, they are treated as wildcards and silently match the wrong rows.
WHERE col LIKE '%' -- misses NULLsWHERE col LIKE '%' OR col IS NULL'%' matches any non-NULL string. A LIKE predicate on a NULL column always evaluates to UNKNOWN — never TRUE. Add IS NULL explicitly if NULLs must be included.
WHERE username LIKE 'Admin%' -- also matches 'admin'WHERE username LIKE BINARY 'Admin%'MySQL LIKE is case-insensitive for non-binary collations. Use LIKE BINARY or a case-sensitive collation (_bin) if you need case-sensitive matching.
WHERE username LIKE 'admin%' -- misses 'Admin'WHERE username ILIKE 'admin%'PostgreSQL LIKE is case-sensitive. Use ILIKE for case-insensitive matching. ILIKE can be accelerated with a pg_trgm GIN index.
WHERE path LIKE '/home/User%' -- misses '/home/user%'WHERE lower(path) LIKE '/home/user%'SQLite LIKE is case-insensitive only for 7-bit ASCII letters. Unicode characters are compared case-sensitively. Normalise with lower() or use GLOB for predictable behaviour.
WHERE col LIKE ''WHERE col = ''LIKE '' matches only the exact empty string — but = '' is clearer, faster, and avoids any edge case with the ESCAPE clause. Reserve LIKE for when wildcards are actually needed.
When LIKE is enough, when REGEXP is necessary, and when full-text search is the better tool
Broader guide to regex operators and practical SQL matching patterns.
Better fit than LIKE or REGEXP when you need large-scale contains search.
Explains why prefix LIKE can be fast while contains search often scans.
Helpful when regex-derived SQL gets assembled into larger dynamic queries.
Relevant when search patterns or filters come from user input and should never turn into unsafe SQL assembly.
Use the broader workflow hub when regex-derived filters need performance review or safer query assembly.
Clean up the query after you paste in the generated clause.
Check the final query before running it on your database.
Generate sample rows to test your new search condition.