1
Syntax Simplicity: LIKE uses simple wildcards % (any sequence of characters) and _ (any single character), whereas REGEXP uses full regular expressions with more complex patterns.
2
Pattern Complexity: LIKE can match basic patterns, e.g., 'A%' finds strings starting with 'A'. REGEXP can handle ranges, repetitions, anchors, alternations, and more advanced patterns.
3
Case Sensitivity: By default, LIKE is case-insensitive for non-binary strings in MySQL, while REGEXP is case-insensitive unless the BINARY keyword is used.
4
Performance: LIKE is generally faster for simple patterns because it uses index optimization in some cases, while REGEXP is more computationally intensive due to regex evaluation.
5
Flexibility: REGEXP supports character classes ([a-z]), quantifiers (*, +, {n,m}), anchors (^, $), and alternation (|), which are not possible with LIKE.

In summary, use LIKE for simple, straightforward pattern matching where performance matters, and use REGEXP when you need advanced, flexible pattern matching or validation.

Example: LIKE Operator

Example: REGEXP Operator

Difficulty: 5/10

Topics: pattern matching, performance, index usage

Scenario Questions

0-2 years experience

1
You need to find all users whose email ends with '@example.com' in a MySQL table. Would you use LIKE or REGEXP, and how would you write the query?
2
If a query using LIKE '%test_' returns rows you didn't expect, what might be causing the mismatch and how would you correct it?
3
When switching from LIKE to REGEXP to match a digit at the start of a string, how does the pattern syntax change?

2-5 years experience

1
Your feature filters product names containing either 'Pro' or 'Max' regardless of case. The current implementation uses LIKE and is failing for some cases. Explain why and propose a fix using REGEXP.
2
A query with REGEXP '^abc.*' is running slowly on a large table. Discuss the trade‑offs between keeping REGEXP versus rewriting it with LIKE 'abc%' and how you would decide.
3
A bug shows that LIKE '%_%' is not treating the underscore as a literal. How would you adjust the query, and would REGEXP be a better choice?

5-8 years experience

1
Your service stores free‑form tags in a column and must support searches like tags starting with 'dev' and ending with a number. Design an indexing strategy that balances REGEXP flexibility with performance, comparing it to a LIKE‑only approach.
2
You are migrating a legacy reporting system that heavily uses REGEXP to a newer MySQL version where REGEXP is slower. How would you refactor those queries, and what considerations matter for maintainability and query plans?
3
Explain how MySQL evaluates LIKE versus REGEXP in terms of optimizer usage, and describe how you would rewrite a REGEXP‑heavy query to take advantage of index scans.

8+ years experience

1
Across dozens of services some teams use LIKE and others use REGEXP, leading to inconsistent performance and bugs. Propose a governance model, including guidelines, tooling, and a migration path to standardize pattern matching.
2
Your organization is moving from MySQL to a distributed SQL platform that only supports LIKE‑style pattern matching. How would you assess the impact on existing REGEXP‑heavy queries, and what architectural changes would you recommend?
3
Discuss the long‑term trade‑offs of relying on REGEXP for data validation versus moving validation logic to the application layer, considering security, maintainability, and scaling.

Follow-up Questions

❓Can you give an example where REGEXP is required but LIKE cannot express the pattern?
❓How does MySQL’s optimizer treat LIKE versus REGEXP when choosing an execution plan?
❓What role does collation play in the results of these operators?