SQL Pattern Matching with LIKE and Wildcards

Up to this point, every text filter you have written uses exact matching. WHERE city = 'Chicago' only finds rows where the city is precisely "Chicago", nothing more, nothing less. But what if you want to find all customers whose name starts with "A"? Or all products that contain the word "Book" somewhere in their name? Or all email addresses that end with a specific domain?

Exact matching cannot help you here. You need pattern matching, and SQL provides it through the LIKE operator combined with two special wildcard characters: % and _.

Pattern matching is the engine behind search bars, autocomplete suggestions, and filtered lists in nearly every application. This guide covers everything you need to know about LIKE and wildcards: how each wildcard works, how to combine them for powerful patterns, case sensitivity behavior across databases, PostgreSQL's ILIKE shortcut, and real-world examples that mirror what you will build in actual applications. Every example uses the ShopSmart sample database with full outputs (we defined it in a previous guide here).

How LIKE Works

The LIKE operator replaces = in a WHERE clause when you want to match a pattern instead of an exact value:

SELECT columns
FROM table
WHERE column LIKE 'pattern';

The pattern is a string enclosed in single quotes that can contain regular characters mixed with wildcard characters. Regular characters must match exactly. Wildcard characters match flexibly.

SQL provides two wildcards:

Let us explore each one in detail.

The % Wildcard (Any Number of Characters)

The percent sign (%) is the most commonly used wildcard. It matches zero, one, or many characters of any kind. Think of it as "anything can go here, including nothing."

Starts With: Pattern at the Beginning

To find values that start with specific characters, place % at the end of the pattern:

-- Products whose name starts with 'W'
SELECT name, price
FROM products
WHERE name LIKE 'W%';

Output:

The pattern 'W%' means: starts with "W", followed by anything. Only "Wireless Mouse" matches.

-- Products whose name starts with 'S'
SELECT name, price
FROM products
WHERE name LIKE 'S%';

Output:

Both "SQL for Beginners" and "Stainless Water Bottle" start with "S".

You can match longer prefixes too:

-- Products whose name starts with 'Blue'
SELECT name, price
FROM products
WHERE name LIKE 'Blue%';

Output:

Ends With: Pattern at the End

To find values that end with specific characters, place % at the beginning:

-- Products whose name ends with 'Mouse'
SELECT name, price
FROM products
WHERE name LIKE '%Mouse';

Output:

-- Customers whose email ends with '@email.com'
SELECT first_name, last_name, email
FROM customers
WHERE email LIKE '%@email.com';

Output:

Every customer's email ends with "@email.com", so they all match.

Contains: Pattern Anywhere in the String

To find values that contain specific characters anywhere, place % on both sides:

-- Products whose name contains 'Book'
SELECT name, price
FROM products
WHERE name LIKE '%Book%';

Output:

Wait, "Data Science Handbook" does not contain "Book"? Let us look more carefully. "Handbook" does contain "Book". The pattern '%Book%' matches any string where "Book" appears anywhere, and "Handbook" includes "book" as a substring.

Actually, this depends on case sensitivity, which we will cover shortly. In a case-sensitive database, '%Book%' would not match "Handbook" because "book" (lowercase b) is different from "Book" (uppercase B). Let us try a safer example:

-- Products whose name contains 'Key'
SELECT name, price
FROM products
WHERE name LIKE '%Key%';

Output:

"Mechanical Keyboard" contains the substring "Key", so it matches.

-- Products whose name contains 'er'
SELECT name, price
FROM products
WHERE name LIKE '%er%';

Output:

Multiple % Wildcards

You can use % multiple times in a single pattern to match more complex structures:

-- Products whose name starts with 'S' and contains 'Beg'
SELECT name, price
FROM products
WHERE name LIKE 'S%Beg%';

Output:

The pattern 'S%Beg%' means: starts with "S", followed by anything, then "Beg", followed by anything. "SQL for Beginners" matches perfectly.

-- Customers whose name starts with a letter and has 'son' somewhere in their last name
SELECT first_name, last_name
FROM customers
WHERE last_name LIKE '%son%';

Output:

Both "Johnson" and "Wilson" contain the substring "son".

The _ Wildcard (Single Character)

The underscore (_) matches exactly one character, no more, no less. It is more precise than % and useful when you know the structure of the data but not specific characters.

Basic _ Examples

-- Find 3-letter city names (unlikely in our data, but demonstrates the concept)
SELECT DISTINCT city
FROM customers
WHERE city LIKE '___';

Three underscores (___) match any string that is exactly 3 characters long.

A more practical example with our data:

-- Customers whose first name is exactly 3 characters long
SELECT first_name, last_name
FROM customers
WHERE first_name LIKE '___';

Output:

"Bob" and "Eva" are each exactly 3 characters. "Alice" (5), "Carol" (5), "David" (5), "Frank" (5), and "Grace" (5) are all too long.

Combining _ with Fixed Characters

You can mix underscores with specific characters to match precise positions:

-- Products whose name has 'a' as the second character
SELECT name
FROM products
WHERE name LIKE '_a%';

Output:

The pattern '_a%' means: any first character, then "a", then anything. "Data Science Handbook" has "a" as its second character.

-- Customers whose first name starts with any character followed by 'a'
SELECT first_name, last_name
FROM customers
WHERE first_name LIKE '_a%';

Output:

"David" has "a" in the second position.

Combining _ and %

Underscores and percent signs can be used together for sophisticated patterns:

-- Products whose name has 'S' as the second character
SELECT name
FROM products
WHERE name LIKE '_S%';

Output:

"USB-C Hub" has "S" in the second position.

-- Customers whose email has exactly 3 characters before the '@' symbol
SELECT first_name, email
FROM customers
WHERE email LIKE '___@%';

Output:

The pattern '___@%' means: exactly 3 characters, then "@", then anything. "bob@email.com" and "eva@email.com" both have 3-character usernames.

NOT LIKE: Excluding Patterns

Just as NOT inverts other conditions, NOT LIKE finds rows that do not match a pattern:

-- Products whose name does NOT start with 'S'
SELECT name, price
FROM products
WHERE name NOT LIKE 'S%';

Output:

8 products are returned. The 2 products starting with "S" ("SQL for Beginners" and "Stainless Water Bottle") are excluded.

-- Customers whose email does NOT end with '@email.com'
SELECT first_name, email
FROM customers
WHERE email NOT LIKE '%@email.com';

Output:

(empty result set)

All customers in our sample data have emails ending with "@email.com", so no rows match the NOT LIKE condition.

Case Sensitivity with LIKE

How LIKE handles uppercase and lowercase letters varies between databases. This is one of the most important practical details to understand.

Default Behavior by Database

Case-Sensitive Behavior (PostgreSQL, SQLite)

In PostgreSQL and SQLite, LIKE distinguishes between uppercase and lowercase:

-- PostgreSQL: Case-sensitive LIKE
SELECT name FROM products WHERE name LIKE '%mouse%';
-- Returns: NO ROWS (the stored value is 'Wireless Mouse', capital M)

SELECT name FROM products WHERE name LIKE '%Mouse%';
-- Returns: Wireless Mouse

This means you must know the exact casing of the stored data, or handle it explicitly.

Case-Insensitive Behavior (MySQL, SQL Server)

In MySQL and SQL Server (with default collation settings), LIKE ignores case:

-- MySQL: Case-insensitive LIKE
SELECT name FROM products WHERE name LIKE '%mouse%';
-- Returns: Wireless Mouse (matches despite different case)

SELECT name FROM products WHERE name LIKE '%MOUSE%';
-- Returns: Wireless Mouse (also matches)

Making LIKE Case-Insensitive (Cross-Database Solution)

The most portable way to perform case-insensitive pattern matching is to convert both sides to the same case using LOWER() or UPPER():

-- Works consistently in ALL databases
SELECT name, price
FROM products
WHERE LOWER(name) LIKE '%mouse%';

Output:

By converting the column value to lowercase with LOWER(name) and writing the pattern in lowercase ('%mouse%'), the comparison works regardless of how the data is stored or which database you use.

-- Find products containing 'pro' (case-insensitive, works everywhere)
SELECT name, price
FROM products
WHERE LOWER(name) LIKE '%pro%';

Output:

-- Find products containing 'book' (case-insensitive, works everywhere)
SELECT name, price
FROM products
WHERE LOWER(name) LIKE '%book%';

Output:

"Data Science Handbook" contains "book" when compared in lowercase.

ILIKE: PostgreSQL's Case-Insensitive Shortcut

PostgreSQL offers a convenient alternative: the ILIKE operator. It works exactly like LIKE but ignores case without needing LOWER():

-- PostgreSQL only: ILIKE for case-insensitive matching
SELECT name, price
FROM products
WHERE name ILIKE '%mouse%';

Output:

-- All of these match in PostgreSQL with ILIKE
SELECT name FROM products WHERE name ILIKE '%keyboard%';
SELECT name FROM products WHERE name ILIKE '%KEYBOARD%';
SELECT name FROM products WHERE name ILIKE '%Keyboard%';
-- All return: Mechanical Keyboard

ILIKE with Other Patterns

ILIKE supports all the same wildcard patterns as LIKE:

-- Products starting with 's' (case-insensitive)
SELECT name
FROM products
WHERE name ILIKE 's%';

Output:

-- Products ending with 'um' (case-insensitive)
SELECT name
FROM products
WHERE name ILIKE '%um';

Output:

NOT ILIKE

-- Products whose name does NOT contain 'er' (case-insensitive)
SELECT name
FROM products
WHERE name NOT ILIKE '%er%';

Output:

Real-World Example: Building a Product Search

Let us build a realistic product search feature step by step, the kind you would implement in an actual e-commerce application.

Basic Search

A user types "shoe" into the search bar. Find all matching products:

SELECT name, price, is_available
FROM products
WHERE LOWER(name) LIKE '%shoe%';

Output:

Search with Availability Filter

Only show products that are currently available:

SELECT name, price
FROM products
WHERE LOWER(name) LIKE '%shoe%'
  AND is_available = true;

Output:

Search with Sorting and Limit

Show the top 5 matching products sorted by price, cheapest first:

SELECT name, price
FROM products
WHERE LOWER(name) LIKE '%s%'
  AND is_available = true
ORDER BY price ASC
LIMIT 5;

Output:

The search term "s" is broad, matching any product with the letter "s" anywhere in the name. The results are filtered to available products, sorted by price, and limited to 5.

Multi-Term Search

What if the user searches for "water bottle"? You can combine multiple LIKE conditions:

-- Match products containing both 'water' AND 'bottle'
SELECT name, price
FROM products
WHERE LOWER(name) LIKE '%water%'
  AND LOWER(name) LIKE '%bottle%';

Output:

This ensures both words appear in the name, regardless of order. "Stainless Water Bottle" matches because it contains both "water" and "bottle."

Searching Across Multiple Columns

Search for a term in either the product name or the category description:

SELECT p.name, p.price, c.name AS category
FROM products p
JOIN categories c ON p.category_id = c.id
WHERE LOWER(p.name) LIKE '%science%'
   OR LOWER(c.description) LIKE '%science%';

Output:

Combining LIKE with Other Operators

LIKE integrates smoothly with AND, OR, NOT, and all other SQL clauses:

Multiple LIKE Conditions with OR

-- Products that start with 'W' OR start with 'B'
SELECT name, price
FROM products
WHERE name LIKE 'W%'
   OR name LIKE 'B%';

Output:

LIKE with AND for Refined Filtering

-- Products containing 'a' in the name AND priced under $40
SELECT name, price
FROM products
WHERE LOWER(name) LIKE '%a%'
  AND price < 40;

Output:

LIKE Inside Complex Conditions

-- Find products that:
-- (contain 'key' in the name) OR (are in category 4 AND contain 'mat' in the name)
SELECT name, price, category_id
FROM products
WHERE LOWER(name) LIKE '%key%'
   OR (category_id = 4 AND LOWER(name) LIKE '%mat%');

Output:

Escaping Wildcards

What if you need to search for a literal % or _ character in your data? Since these characters have special meaning in LIKE patterns, you need to escape them.

The Problem

Imagine a product named "50% Off Bundle" or a code like "ITEM_001". If you search using LIKE, the % and _ will be interpreted as wildcards:

-- Trying to find '50%' literally, but % is treated as a wildcard
WHERE name LIKE '%50%%'
-- This matches: anything + "50" + anything + anything
-- Not what we intended!

The Solution: ESCAPE Clause

Use the ESCAPE clause to designate a character that neutralizes the wildcard:

-- Find product names containing a literal '%' character
SELECT name
FROM products
WHERE name LIKE '%50\%%' ESCAPE '\';
-- The '\%' means a literal percent sign

-- Find values containing a literal underscore
SELECT name
FROM products
WHERE name LIKE '%\_001%' ESCAPE '\';
-- The '\_' means a literal underscore

The backslash (\) is the most common escape character, but you can designate any character:

-- Using '!' as the escape character
WHERE name LIKE '%50!%%' ESCAPE '!'

Performance Considerations

LIKE patterns have significant performance implications that are important to understand, especially when working with large tables.

Leading Wildcards Prevent Index Use

-- Fast: The database can use an index on the 'name' column
WHERE name LIKE 'Wire%'
-- The database knows to look for values starting with 'Wire'

-- Slow: The database CANNOT use an index
WHERE name LIKE '%Mouse%'
-- The database must scan every single row to check for 'Mouse'

When the pattern starts with %, the database has no way to narrow down the search using an index. It must perform a full table scan, checking every row. On a table with millions of rows, this can be dramatically slower.

Practical Exercises

Exercise 1

Find all products whose name starts with "M".

SELECT name, price
FROM products
WHERE name LIKE 'M%';

Expected output:

Exercise 2

Find all customers whose email contains "bob".

SELECT first_name, last_name, email
FROM customers
WHERE LOWER(email) LIKE '%bob%';

Expected output:

Exercise 3

Find all products whose name ends with a word ending in "er" (like "Speaker" or "Maker").

SELECT name, price
FROM products
WHERE LOWER(name) LIKE '%er%';

Expected output:

Exercise 4

Find all customers whose first name is exactly 5 characters long.

SELECT first_name, last_name
FROM customers
WHERE first_name LIKE '_____';

Expected output:

Exercise 5

Find all products that contain both "a" and "e" in their name (case-insensitive), sorted by price ascending.

SELECT name, price
FROM products
WHERE LOWER(name) LIKE '%a%'
  AND LOWER(name) LIKE '%e%'
ORDER BY price ASC;

Expected output:

Key Takeaways

Pattern matching with LIKE and wildcards gives you flexible text searching capabilities that exact matching cannot provide. Here is what you should remember:

• LIKE replaces = in WHERE for pattern-based matching
• % matches any number of characters (including zero): use it for starts-with, ends-with, and contains searches
• _ matches exactly one character: use it when you know the position but not the value of a character
• The three essential patterns are: 'A%' (starts with), '%ing' (ends with), and '%term%' (contains)
• LIKE is case-sensitive in PostgreSQL and SQLite, but case-insensitive in MySQL and SQL Server (by default)
• Use LOWER() on both sides of the comparison for reliable case-insensitive matching across all databases
• ILIKE is PostgreSQL's built-in case-insensitive alternative to LIKE, but it is not portable
• NOT LIKE excludes rows matching a pattern
• Escape literal % and _ characters using the ESCAPE clause
• Patterns starting with % prevent index usage and cause full table scans on large datasets
• For heavy text searching at scale, consider full-text search features instead of LIKE

LIKE is one of the most practical tools in your SQL toolkit. It powers search bars, filters, autocomplete, and data validation across virtually every application that interacts with a database.