LIKE Operator
Learn how the LIKE operator lets you search text columns for patterns instead of exact matches.
Introduction
Everything you have filtered with so far — =, BETWEEN, IN — requires knowing the exact value you are looking for. But what if you only know part of a value, like a name starting with a certain letter, or an email containing a certain word? That is exactly the job of the LIKE operator.
This lesson introduces LIKE for pattern matching on text, walks through a worked example, and covers NOT LIKE for excluding a pattern.
- What the LIKE operator does and when to reach for it.
- How to match text that starts with a specific letter or sequence.
- How NOT LIKE excludes rows matching a pattern.
- A preview of the dedicated wildcards lesson coming up next.
What is LIKE?
LIKE is used inside a WHERE clause to search for a pattern within a text column, rather than requiring an exact match. It is paired with special wildcard characters that stand in for unknown parts of the text.
We will use our familiar students table, with columns id and name.
| id | name |
|---|---|
| 1 | Alex |
| 2 | Arjun |
| 3 | Priya |
| 4 | John |
| 5 | Amit |
| 6 | Sana |
| 7 | Sanjay |
Syntax
column_name LIKE 'pattern'The pattern is a text string that can include the wildcard character %, which matches any sequence of characters (including none at all). The full mechanics of wildcards get their own dedicated lesson right after this one — for now, just focus on the most common pattern: matching the start of a string.
Example: Names Starting with A
To find every student whose name starts with the letter 'A', place the % wildcard immediately after the 'A':
SELECT name
FROM students
WHERE name LIKE 'A%';+-------+
| name |
+-------+
| Alex |
| Arjun |
| Amit |
+-------+The pattern 'A%' reads as "starts with A, followed by anything." Priya, John, Sana, and Sanjay are all excluded because none of them begin with the letter A.
By default in MySQL, LIKE comparisons are typically case-insensitive on most default configurations, so 'A%' will usually also match a name stored as 'alex'. This depends on the column's collation, a detail you do not need to worry about yet.
NOT LIKE
Adding NOT before LIKE reverses the match, returning every row whose value does not fit the given pattern.
SELECT name
FROM students
WHERE name NOT LIKE 'A%';+---------+
| name |
+---------+
| Priya |
| John |
| Sana |
| Sanjay |
+---------+This returns exactly the students excluded from the earlier example — everyone whose name does not start with 'A'.
A Deeper Dive Is Coming: Wildcards
So far you have only seen the % wildcard used at the end of a pattern. But wildcards support far richer patterns — matching the end of a string, the middle of a string, or an exact position — and there is a second wildcard character, _, that you have not seen yet.
The very next lesson is dedicated entirely to wildcards, covering both % and _ in depth with a full table of pattern examples. This lesson intentionally kept things simple with just the "starts with" pattern so you could focus on LIKE itself first.
Common Mistakes
- Forgetting the % wildcard entirely and writing LIKE 'A', which only matches the single exact value 'A', not names starting with A.
- Using = instead of LIKE when a pattern is intended, which silently performs an exact match instead of a search.
- Assuming LIKE is always case-sensitive — behavior depends on the column's collation settings.
- Forgetting to quote the pattern string, since it is text and must be wrapped in quotes.
Best Practices
- Use LIKE only when you genuinely need pattern matching — plain = is faster for exact matches.
- Keep patterns as specific as possible to avoid unintentionally broad matches.
- Be aware that patterns starting with % (like '%son') can be slow on very large tables, since they prevent efficient index usage.
- Test your pattern on a small sample of data first to confirm it matches what you expect.
Frequently Asked Questions
Is LIKE case-sensitive?
By default, most MySQL text columns use a case-insensitive collation, so LIKE comparisons are typically case-insensitive too. This can be changed with a different collation if case-sensitive matching is required.
Can LIKE be used on numeric columns?
MySQL will implicitly convert numbers to text to allow LIKE to run, but it is not idiomatic — LIKE is intended for text pattern matching, and numeric comparisons should use standard operators instead.
What is the difference between LIKE and =?
= requires an exact, complete match. LIKE allows partial matching through wildcard patterns, making it suited for searches rather than precise lookups.
Does LIKE work without any wildcard characters?
Yes, but then it behaves exactly like =, since there is no wildcard to allow partial matching.
Key Takeaways
- LIKE performs pattern matching on text columns rather than requiring an exact match.
- The % wildcard matches any sequence of characters, letting you match a name like 'A%' for "starts with A."
- NOT LIKE reverses the match, returning rows that do not fit the pattern.
- A full breakdown of every wildcard character is covered in the very next lesson.
Summary
LIKE opens the door to searching text instead of only matching it exactly, using wildcard patterns like 'A%' to find values that start with a given sequence.
In this lesson, you learned what LIKE does, saw it used to find names starting with a letter, and learned how NOT LIKE excludes a pattern. Next, you will take a deep dive into every wildcard character LIKE supports.
- You understand what the LIKE operator is used for.
- You can match text starting with a given letter or sequence.
- You know how NOT LIKE excludes a pattern.
- You are ready for a deeper dive into wildcards.