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.
LIKE handles "starts with". Regex handles "a valid email address".
A regular expression describes a shape that text may take, rather than the text itself. SQL's LIKE offers exactly two wildcards; regex offers character classes, quantifiers, anchors, alternation and grouping — enough to validate, extract and clean data directly in the database.
The dialect differences are mostly in the operator name; the pattern syntax itself is broadly POSIX or PCRE and transfers well.
Try the email-validation pattern in this lab and read the LIKE equivalent underneath it. LIKE can express "contains an @" and little more; it cannot say "one or more of these characters", "exactly four digits", or "either gmail or yahoo". Those are quantifiers, character classes and alternation — the things LIKE simply does not have.
One more difference that catches people: LIKE 'abc' must match the whole string, whereas most SQL regex operators match if the pattern is found anywhere in it. That is why anchors ^ and $ matter so much — toggle them in this lab and watch the match count change.
A regex in a WHERE clause is applied row by row and cannot use a normal B-tree index. On a large table that is a full scan every time. Practical guidance:
Reach for regex when the shape of the text matters and LIKE cannot describe it — validation, extraction, messy-data cleanup. Reach for LIKE when a prefix will do, because it can use an index and regex usually cannot. This lab runs your pattern through a real regex engine, so what matches here is what will match in your database.
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.