SQL LIKE and Wildcards: Pattern Matching Made Simple

Searching for an exact value in a database is easy — you just use =. But what do you do when you only know part of the value? Maybe you want to find all customers whose email ends in @gmail.com, or all products with "pro" somewhere in the name. That's where SQL's LIKE operator comes in.

In this guide you'll learn how LIKE works, how to use both wildcard characters, and the gotchas that trip people up. By the end you'll be writing confident pattern-matching queries.

The LIKE Operator

LIKE is used in a WHERE clause to match a column value against a pattern string. If the value matches the pattern, the row is included in the results.

SELECT *
FROM customers
WHERE email LIKE '[e-mail]';

The % is a wildcard — we'll cover exactly what it means in a moment.

Let's use a concrete table for all examples. Imagine a products table:

CREATE TABLE products (
    product_id   INT PRIMARY KEY,
    name         VARCHAR(100),
    category     VARCHAR(50),
    sku          VARCHAR(20)
);

With data like this:

product_id	name	category	sku
1	Wireless Mouse Pro	Electronics	EL-001-A
2	Mouse Pad XL	Accessories	AC-002-B
3	Mechanical Keyboard	Electronics	EL-003-A
4	USB-C Hub Pro	Electronics	EL-004-C
5	Laptop Stand	Accessories	AC-005-A
6	ProCam Webcam	Electronics	EL-006-B

The Two Wildcard Characters

SQL's LIKE operator uses two special wildcard characters:

% — matches zero or more characters of any kind

_ — matches exactly one character

That's it. Mastering LIKE is just about knowing when to use each.

Using % (Percent) — Any Length Match

The percent sign is the wildcard you'll reach for most often. It can stand in for any number of characters, including zero.

Starts with a pattern:

-- Products whose name starts with "Mouse"
SELECT name FROM products WHERE name LIKE 'Mouse%';

Result: Mouse Pad XL

Ends with a pattern:

-- Products whose name ends with "Pro"
SELECT name FROM products WHERE name LIKE '%Pro';

Result: Wireless Mouse Pro, USB-C Hub Pro

Contains a pattern anywhere:

-- Products with "pro" anywhere in the name (case-insensitive in many DBs)
SELECT name FROM products WHERE name LIKE '%Pro%';

Result: Wireless Mouse Pro, USB-C Hub Pro, ProCam Webcam

Notice that ProCam Webcam matched too — %Pro% matches "Pro" at the start, middle, or end.

Combining multiple percent signs:

-- SKUs that start with "EL" and end with "A"
SELECT name, sku FROM products WHERE sku LIKE 'EL%A';

name	sku
Wireless Mouse Pro	EL-001-A
Mechanical Keyboard	EL-003-A
Laptop Stand	AC-005-A

Wait — Laptop Stand (SKU AC-005-A) didn't match because its SKU starts with AC, not EL. Only EL-001-A and EL-003-A would actually be returned. The pattern EL%A means: starts with "EL", then any characters, then ends with "A".

Using _ (Underscore) — Single Character Match

The underscore matches exactly one character — no more, no less. It's useful when you know the structure of a value but not one specific character.

-- SKUs where the category code is 2 characters, followed by "-00", then any digit, then "-A"
SELECT name, sku FROM products WHERE sku LIKE '__-00_-A';

Each _ stands for exactly one character:

name	sku
Wireless Mouse Pro	EL-001-A
Mechanical Keyboard	EL-003-A
Laptop Stand	AC-005-A

A more practical example: finding phone numbers with a specific area code structure.

-- Phone numbers matching the pattern (555) ###-####
SELECT name, phone FROM contacts WHERE phone LIKE '(555) ___-____';

NOT LIKE — Excluding Patterns

Just like NOT IN or NOT NULL, you can flip LIKE with NOT:

-- Products that are NOT in the Electronics category (by SKU prefix)
SELECT name, sku FROM products WHERE sku NOT LIKE 'EL%';

Result: Mouse Pad XL (AC-002-B) and Laptop Stand (AC-005-A).

Case Sensitivity: It Depends on Your Database

This is one of the most common sources of confusion with LIKE:

• MySQL: Case-insensitive by default for text columns with standard collation
• PostgreSQL: Case-sensitive by default — use ILIKE for case-insensitive matching
• SQLite: Case-insensitive for ASCII characters only
• SQL Server: Depends on the column's collation (often case-insensitive)

-- PostgreSQL: this WON'T match "Wireless Mouse Pro"
SELECT name FROM products WHERE name LIKE '%pro%';

-- PostgreSQL: use ILIKE for case-insensitive
SELECT name FROM products WHERE name ILIKE '%pro%';
-- Returns: Wireless Mouse Pro, USB-C Hub Pro, ProCam Webcam

Always check your database's collation settings if results look unexpected.

Escaping Wildcards — When % or _ Are Literal Characters

What if you need to search for a literal percent sign or underscore — like finding discount codes that contain _? You need to escape them with an escape character.

-- Find discount codes that literally contain an underscore
SELECT code FROM discount_codes WHERE code LIKE '%\_%' ESCAPE '\';

-- Or in PostgreSQL, the default escape character is \
SELECT code FROM discount_codes WHERE code LIKE '%\_%';

Most databases use \ as the default escape character, but you can define your own with the ESCAPE keyword.

Common Mistakes to Avoid

1. Leading wildcard kills index usage

-- ❌ This forces a full table scan — no index can help
SELECT * FROM products WHERE name LIKE '%Mouse%';

-- ✅ A leading literal is index-friendly
SELECT * FROM products WHERE name LIKE 'Mouse%';

If you frequently search with a leading %, consider a full-text search solution instead (PostgreSQL has tsvector/tsquery, MySQL has FULLTEXT indexes).

2. Forgetting case sensitivity differences across databases

A query that works in MySQL may silently return wrong results in PostgreSQL. Use ILIKE (PostgreSQL) or LOWER() to be explicit:

SELECT * FROM products WHERE LOWER(name) LIKE LOWER('%pro%');

3. Using LIKE when you mean =

If you're matching a complete, exact value with no wildcards, just use = — it's faster and clearer:

-- ❌ Unnecessary LIKE
WHERE category LIKE 'Electronics'

-- ✅ Just use equals
WHERE category = 'Electronics'

4. Expecting _ to match zero characters

The underscore always matches exactly one character. 'Pro_' will NOT match 'Pro' — it needs at least one more character after "Pro".

Putting It All Together

Here's a realistic query combining several concepts — finding electronics products with "Pro" in the name and an "A"-grade SKU:

SELECT
    product_id,
    name,
    sku,
    category
FROM products
WHERE
    category = 'Electronics'
    AND name ILIKE '%pro%'         -- case-insensitive "pro" anywhere in name
    AND sku LIKE '%-A';            -- SKU ends with "-A" (A-grade)
ORDER BY name;

product_id	name	sku	category
1	Wireless Mouse Pro	EL-001-A	Electronics

Key Takeaways

• LIKE matches values against a pattern using two wildcards: % (any number of characters) and _ (exactly one character)
• NOT LIKE excludes rows that match a pattern
• Case sensitivity depends on your database — PostgreSQL uses ILIKE for case-insensitive matching
• Leading wildcards (LIKE '%value') disable index usage — avoid them on large tables where performance matters
• Use = instead of LIKE when you don't need pattern matching
• Escape literal % or _ characters using the ESCAPE keyword

Pattern matching with LIKE comes up constantly — searching user-inputted names, filtering SKUs, querying log messages, and more. Once you internalize the two wildcards and the case-sensitivity quirk, you'll reach for LIKE with confidence.

---

Do you have a favourite LIKE pattern you use regularly? Or ran into a tricky case-sensitivity bug? Share it in the comments — I'd love to hear how you use pattern matching in the real world!