SQL
MySQL
database query
LIKE operator
multiple values

MySQL Like multiple values

System Design practice on Codemia

Work through 120+ system design problems with detailed solutions, from rate limiters to multi-region storage.

Practice system design

Introduction

MySQL does not have a single special syntax that means "apply LIKE to many patterns at once." Instead, you combine several LIKE conditions with OR, switch to regular expressions, or model the patterns as data and join against them. The right choice depends on how many patterns you have and whether the query is a one-off filter or a recurring search feature.

The simplest answer: chain LIKE with OR

For a small fixed number of patterns, the plain SQL answer is straightforward:

sql
1SELECT *
2FROM products
3WHERE name LIKE 'phone%'
4   OR name LIKE 'tablet%'
5   OR name LIKE 'watch%';

This is the normal way to say "match any of these patterns." It is easy to read and appropriate when the set of patterns is small and known at query-writing time.

Use parameters in application code

If the patterns come from application input, parameterize them instead of concatenating raw SQL strings.

python
patterns = ["phone%", "tablet%", "watch%"]
where_sql = " OR ".join(["name LIKE %s"] * len(patterns))
sql = f"SELECT * FROM products WHERE {where_sql}"

The important idea is that each pattern still becomes its own LIKE predicate. There is no native LIKE IN (...) construct in MySQL.

Regular expressions can be cleaner for many patterns

When you have many related patterns, a regular expression can be more compact than a long chain of OR conditions.

sql
SELECT *
FROM products
WHERE name REGEXP '^(phone|tablet|watch)';

This is often easier to maintain than ten separate LIKE clauses, but it is not automatically faster. Regex matching is more expressive, yet often more expensive than simple prefix comparisons.

Store patterns in a table when they are real data

If the patterns are managed by the application, not hard-coded in one query, store them in a table and join logically against them.

sql
1SELECT DISTINCT p.*
2FROM products AS p
3JOIN search_patterns AS s
4  ON p.name LIKE s.pattern;

This is useful when business users or administrators define the search rules. It turns "many patterns" from a query-construction problem into a data-model problem.

Performance matters, especially with leading wildcards

The biggest performance rule with LIKE is that a leading wildcard usually prevents useful index range matching:

  • 'name LIKE 'phone%' can often benefit from an index'
  • 'name LIKE '%phone%' usually cannot'

So if your patterns mostly start with %, the problem is closer to text search than simple filtering. At that point, a full-text index or dedicated search tool may be more appropriate than stacking more LIKE predicates.

Pick the right tool for the shape of the problem

As a rough guide:

  • use OR plus LIKE for a few patterns
  • use REGEXP for compact expressive matching
  • use a pattern table for dynamic rule sets
  • use full-text search when the problem is really free-text search

That progression usually reflects increasing complexity in the search feature, not just a different SQL spelling.

Common Pitfalls

The biggest mistake is searching for a nonexistent syntax like column LIKE IN (...). MySQL does not support that.

Another issue is building long unparameterized SQL strings from user input. If the patterns are dynamic, parameterize them safely in application code.

Developers also underestimate the cost of leading wildcards such as %term%. Those often prevent efficient index usage.

Finally, a very large number of pattern checks may indicate the wrong storage or search design. Sometimes the fix is not more SQL syntax, but a better search model.

Summary

  • MySQL handles multiple LIKE patterns by combining predicates, usually with OR.
  • There is no built-in LIKE IN (...) syntax.
  • 'REGEXP can be a cleaner alternative when many patterns share a structure.'
  • Prefix patterns are much friendlier to indexes than leading-wildcard patterns.
  • If pattern matching becomes a major feature, consider a pattern table or full-text search instead of ever-longer LIKE chains.

Related reading
Course
Beginner
27 lessons
10 hours
System Design Fundamentals

Build a strong foundation in designing scalable, reliable distributed systems.

View the course
Track what you have practised

A free account saves your progress, solutions and study plan across every problem on Codemia.

System Design practice on Codemia

Work through 120+ system design problems with detailed solutions, from rate limiters to multi-region storage.

Practice system design

All Rights Reserved.