SQL LIKE: The Complete Guide to Pattern Matching in Your Database

If you have ever needed to find every customer whose email ends in `@gmail.com`, or every product whose name starts with “Pro”, you have run into the limits of the humble equals sign. The `=` operator only finds exact matches. Real searches are rarely that tidy. This is exactly the gap the SQL LIKE operator was built to fill, and once it clicks, you will reach for it constantly.

Table of Contents

Let me walk you through it slowly. By the end of this guide you will understand how `LIKE` works, how the wildcards behave, when to use it, when *not* to, and one performance trap that quietly catches almost everyone the first time their table gets large.

Key Takeaways
• `LIKE` matches text against a pattern inside a `WHERE` clause, returning rows where a column fits the shape you describe.
• The two wildcards are `%` (any sequence of characters, including none) and `_` (exactly one character).
• Anchor your patterns at the start (`’smith%’`) whenever possible. A leading wildcard (`’%smith’`) cannot use an index and forces a full table scan.
• Use `NOT LIKE` to exclude patterns, and `ESCAPE` when you need to match a literal `%` or `_`.
• When you genuinely need “contains” search at scale, reach for full-text search instead of `LIKE ‘%word%’`.

What does the SQL LIKE operator actually do?

The `LIKE` operator performs pattern matching on text. You place it in a `WHERE` clause, give it a pattern, and the database returns every row whose column value matches that pattern. Think of `=` as asking “is this string *exactly* equal?” and `LIKE` as asking “does this string *fit the shape* I’m describing?”

Here is the simplest possible comparison. Imagine a `customers` table:

“`sql — Exact match: only rows where the name is precisely “Anna” SELECT name FROM customers WHERE name = ‘Anna’;

— Pattern match: every name that begins with “Ann” SELECT name FROM customers WHERE name LIKE ‘Ann%’; “`

The second query returns `Anna`, `Anne`, `Annabel`, and `Anniston` all at once. That flexibility comes entirely from the `%` symbol, which is one of two SQL wildcards that make pattern matching possible.

What are the SQL LIKE wildcards?

Wildcards are placeholder characters that stand in for unknown parts of a string. SQL gives you two of them, and almost everything you will ever do with `LIKE` is a combination of these two.

Wildcard	Matches	Example pattern	Matches values like
`%`	Any sequence of characters, including none	`’a%’`	`a`, `apple`, `avocado`
`_`	Exactly one single character	`’a_’`	`an`, `at`, `ax` (not `a` or `ant`)

The difference is worth pausing on. The percent sign is greedy and flexible: it happily matches zero characters or five hundred. The underscore is strict: it demands exactly one character to occupy its spot, no more and no less. Mixing them gives you precise control.

Here is a reference of the common patterns you will build with them:

Goal	Pattern	Plain-English meaning
Starts with	`’pro%’`	Begins with “pro”, anything after
Ends with	`’%ing’`	Ends with “ing”, anything before
Contains	`’%data%’`	Has “data” somewhere inside
Exactly 3 characters	`’___’`	Three of any character
Second letter is “a”	`’_a%’`	One char, then “a”, then anything
Starts and ends fixed	`’A%z’`	Begins with “A”, ends with “z”

How do I write starts-with, ends-with, and contains searches?

These three patterns cover the vast majority of real work. Let me show each one with example output so you can see exactly what comes back.

Starts with — anchor the literal text on the left and put `%` on the right:

“`sql SELECT name FROM products WHERE name LIKE ‘Pro%’; “`

“` +——————+

| name | +——————+

| Pro Keyboard |

| Pro Webcam HD |

| Professional Kit | +——————+ “`

Ends with — put `%` on the left and the literal text on the right. A classic use is finding emails by domain:

“`sql SELECT email FROM customers WHERE email LIKE ‘%@gmail.com’; “`

“` +———————-+

| email | +———————-+

| [email protected] |

| [email protected] | +———————-+ “`

Contains — wrap the text in `%` on both sides. This is your general “search anywhere” pattern:

“`sql SELECT title FROM articles WHERE title LIKE ‘%backup%’; “`

“` +——————————–+

| title | +——————————–+

| How to back up your database |

| Backup strategies for websites |

| Why backups matter | +——————————–+ “`

Notice the match is case-insensitive in that last result (`backup` matched `Backup`). Whether that happens depends on your database’s collation, which we will cover shortly.

How do I match an exact length or position with the underscore?

The underscore wildcard shines when you care about the *structure* of a value, not just its contents. Each `_` reserves a slot for exactly one character.

“`sql — Product codes that are exactly 5 characters long SELECT code FROM inventory WHERE code LIKE ‘_____’;

— Codes starting with “A”, then any 2 chars, then “9” SELECT code FROM inventory WHERE code LIKE ‘A__9’; “`

“` +——+

| code | +——+

| AB19 |

| AX79 |

| AZ09 | +——+ “`

You can combine `_` and `%` freely. Want a value that starts with a single unknown character followed by “ext” and then anything else? That is `’_ext%’`, which matches `text`, `nextstep`, and `Sextant` but not `extra` (because `extra` has no character before `ext`).

Can I combine wildcards in one pattern?

Absolutely, and this is where `LIKE` becomes genuinely expressive. You can stack multiple wildcards to describe surprisingly specific shapes.

“`sql — Phone numbers formatted as (XXX) XXX-XXXX SELECT phone FROM contacts WHERE phone LIKE ‘(___) ___-____’;

— SKUs: 2 letters, a dash, then anything ending in “-RED” SELECT sku FROM products WHERE sku LIKE ‘__-%-RED’; “`

“` +————–+

| sku | +————–+

| KB-PRO-RED |

| MS-WIRELESS-RED | +————–+ “`

The literal characters (the dash, the parentheses, the letters) act as anchors, and the wildcards fill the gaps. Reading a pattern left to right and narrating it in plain English is the fastest way to build confidence here.

Is SQL LIKE case-sensitive?

This is one of those “it depends” answers, and the honest truth is that case sensitivity is decided by your column’s collation, not by `LIKE` itself.

In MySQL and MariaDB, the default collations (those ending in `_ci`, for “case insensitive”) treat `’a%’` and `’A%’` as equivalent. So `LIKE ‘pro%’` will happily match `Professional`. If you want case-sensitive matching, you can force it with a binary comparison:

“`sql — Case-insensitive (default in most MySQL/MariaDB setups) SELECT name FROM products WHERE name LIKE ‘pro%’;

— Case-sensitive: only lowercase “pro…” matches SELECT name FROM products WHERE name LIKE BINARY ‘pro%’; “`

In PostgreSQL, by contrast, `LIKE` is case-sensitive by default, and you use `ILIKE` for case-insensitive matching. The lesson: never assume. Test a small query first so you know how your specific database behaves before you rely on it in production.

How do I exclude patterns with NOT LIKE?

Sometimes you want everything *except* a pattern. Just put `NOT` in front:

“`sql — All emails that are NOT from gmail SELECT email FROM customers WHERE email NOT LIKE ‘%@gmail.com’; “`

`NOT LIKE` is the mirror image of `LIKE`. Every row that `LIKE` would have returned is now excluded, and everything else comes through. It is perfect for filtering out test accounts (`WHERE email NOT LIKE ‘%@test.local’`) or internal staff domains from a customer export.

How do I match a literal % or _ character?

Here is a subtle problem. What if the text you are searching for actually *contains* a percent sign or an underscore? For example, finding discount codes that literally include “50%”. If you write `LIKE ‘%50%%’`, the database reads every `%` as a wildcard and gives you nonsense.

The fix is the `ESCAPE` clause. You nominate an escape character, and any wildcard immediately following it is treated as a literal.

“`sql — Find values containing a literal “50%” SELECT code FROM coupons WHERE code LIKE ‘%50!%%’ ESCAPE ‘!’;

— Find usernames containing a literal underscore SELECT username FROM accounts WHERE username LIKE ‘%!_%’ ESCAPE ‘!’; “`

“` +————-+

| username | +————-+

| admin_user |

| test_2026 | +————-+ “`

In the first query, `!%` means “an actual percent sign,” while the unescaped `%` on either side still acts as a wildcard. You can choose any character as your escape character; `!` and `\` are common picks. Pick one that does not appear in your search text.

What is the hidden performance trap with SQL LIKE?

This is the single most important thing in this entire guide, and almost no one learns it until a query mysteriously slows to a crawl. So let me give it to you plainly.

The position of the wildcard, not its presence, decides whether your query flies or crawls.

A pattern anchored at the start, like `LIKE ‘smith%’`, can use a database index and stays fast even on a table with millions of rows. The database keeps indexes sorted alphabetically by the *start* of each value, so when you say “starts with smith,” it can jump straight to the right neighborhood and stop scanning the moment it leaves it. That is the same reason you can find “Smith” in a phone book in seconds.

But the moment you put a wildcard at the *front*, like `LIKE ‘%smith’` or `LIKE ‘%smith%’`, you have told the database the beginning of the value is unknown. An index sorted by the start is now useless, because the thing you are matching could be anywhere inside any value. The database has no choice but to read every single row and check each one. That is a full table scan.

This is precisely why a “contains” search feels instant on 100 rows during development and then grinds to a halt on a million rows in production. Nothing about the query changed. The table simply got big enough for the missing index to matter.

“`sql — FAST: anchored at start, can use an index SELECT * FROM customers WHERE last_name LIKE ‘Smith%’;

— SLOW on large tables: leading wildcard forces a full scan SELECT * FROM customers WHERE last_name LIKE ‘%smith%’; “`

The lesson is twofold. First, anchor your `LIKE` patterns at the beginning whenever you possibly can. Second, when you genuinely need “contains” or fuzzy search across a large table, do not fight `LIKE`. Reach for full-text search instead, which is purpose-built for that job. If you want to go deeper on how indexes turn a slow scan into a fast lookup, is the natural next read.

When should I use LIKE versus = or full-text search?

It helps to think of three tiers of text searching, each suited to a different need.

Use `=` when you know the exact value. It is the fastest and clearest option. `WHERE status = ‘active’` is unambiguous and index-friendly. Reaching for `LIKE ‘active’` here would just be slower with no benefit.

Use `LIKE` for simple, predictable patterns, especially ones anchored at the start: prefixes, suffixes, fixed-length codes, and the occasional small-table “contains” lookup. It is built into standard SQL and works everywhere.

Use full-text search (or regex) when `LIKE` runs out of room. If you need to match multiple words in any order, rank results by relevance, handle word stems (“run” matching “running”), or search large bodies of text quickly, `LIKE ‘%…%’` is the wrong tool. MySQL and MariaDB offer `MATCH … AGAINST` for exactly this, and it uses a dedicated full-text index. Building search-heavy queries pairs naturally with a solid grasp of and how you around your filters.

DarazHost makes practicing SQL pattern matching effortless. Every DarazHost hosting plan includes MySQL/MariaDB databases with phpMyAdmin built in, so you can write and test `LIKE` queries in a friendly interface without touching the command line. Fast SSD storage means your searches return quickly even as your tables grow, and automatic backups mean you can experiment freely, knowing a clumsy query or a bad import can always be rolled back. You get the database tools to learn pattern matching and everything beyond it, backed by 24/7 support whenever you get stuck. It is a genuinely forgiving place to learn by doing.

Frequently asked questions about SQL LIKE

Does LIKE work on numbers, or only text? `LIKE` is designed for text (string) columns. You *can* apply it to a numeric column, and the database will implicitly convert the number to a string first, but this defeats any numeric index and is usually a sign you should be using a range comparison like `BETWEEN` or `>=` instead.

What is the difference between % and _ again? The `%` wildcard matches any sequence of characters, including none at all, so `’a%’` matches `a` by itself. The `_` wildcard matches exactly one character, so `’a_’` requires `a` plus one more character and will never match `a` alone.

Why is my LIKE query so slow? Almost always because of a leading wildcard. A pattern like `’%term%’` or `’%term’` cannot use an index and forces the database to scan every row. Anchor the pattern at the start (`’term%’`) if you can, or switch to full-text search for large-scale “contains” searches.

Can I use LIKE with multiple patterns at once? Not in a single `LIKE`, but you can combine several with `OR`: `WHERE name LIKE ‘A%’ OR name LIKE ‘B%’`. For matching against a fixed list of exact values, `IN (‘a’, ‘b’, ‘c’)` is cleaner. For complex alternation, regular expressions (`RLIKE` / `REGEXP` in MySQL and MariaDB) are more powerful.

Is LIKE the same in MySQL, PostgreSQL, and SQL Server? The core behavior and the `%` and `_` wildcards are standard across all of them. The main differences are case sensitivity (collation-driven in MySQL/MariaDB, case-sensitive by default in PostgreSQL with `ILIKE` for insensitive matching) and a few vendor extras. The fundamentals you learned here transfer everywhere.

Bringing it together

The `LIKE` operator is one of the most practical tools in SQL precisely because it bridges the gap between rigid exact matching and the messy, partial way people actually search. Master the two wildcards, remember to anchor your patterns at the start for speed, escape literals when you need them, and know the moment to graduate to full-text search. Do that, and pattern matching stops being a mystery and becomes second nature.

For the bigger picture of how databases store, index, and serve your data, this article sits inside our complete guide to MySQL and MariaDB databases for website owners. Read that next to see how every piece fits together.