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:

Wildcard	Meaning	Matches
%	Any sequence of characters (including zero characters)	'', 'a', 'abc', 'anything at all'
_	Exactly one single character	'a', 'B', '7', ' ' (any single character)

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:

name	price
Wireless Mouse	29.99

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:

name	price
SQL for Beginners	34.99
Stainless Water Bottle	24.99

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:

name	price
Bluetooth Speaker	65.00

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:

name	price
Wireless Mouse	29.99

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

Output:

first_name	last_name	email
Alice	Johnson	alice@email.com
Bob	Martinez	bob@email.com
Carol	Singh	carol@email.com
David	Chen	david@email.com
Eva	Brown	eva@email.com
Frank	Wilson	frank@email.com

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:

name	price
Data Science Handbook	42.50

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:

name	price
Mechanical Keyboard	89.99

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

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

Output:

name	price
SQL for Beginners	34.99
Coffee Maker Pro	129.99
Bluetooth Speaker	65
Stainless Water Bottle	24.99

The Three Essential % Patterns

These three patterns cover the vast majority of real-world text searches:

Pattern	Meaning	Example
'A%'	Starts with "A"	WHERE name LIKE 'A%'
'%ing'	Ends with "ing"	WHERE name LIKE '%ing'
'%pro%'	Contains "pro" anywhere	WHERE name LIKE '%pro%'

If you remember only three things about LIKE, remember these three patterns.

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:

name	price
SQL for Beginners	34.99

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:

first_name	last_name
Alice	Johnson
Frank	Wilson

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:

first_name	last_name
Bob	Martinez
Eva	Brown

"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:

name
Data Science Handbook

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:

first_name	last_name
Carol	Singh
David	Chen

"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:

name
USB-C Hub

"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:

first_name	email
Bob	bob@email.com
Eva	eva@email.com

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

Choosing Between % and _

• Use % when you do not know (or do not care) how many characters are in a position
• Use _ when you know exactly how many characters should be there but not what they are

% is flexible (matches 0 or more characters). _ is precise (matches exactly 1 character).

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:

name	price
Wireless Mouse	29.99
Mechanical Keyboard	89.99
USB-C Hub	45.00
Data Science Handbook	42.50
Coffee Maker Pro	129.99
Yoga Mat Premium	38.00
Running Shoes X1	110.00
Bluetooth Speaker	65.00

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.

NOT LIKE and NULL

Just like other comparisons, NOT LIKE does not match NULL values. If a column is NULL, the LIKE comparison returns UNKNOWN, and NOT UNKNOWN is still UNKNOWN.

-- This does NOT return rows where city is NULL
SELECT first_name, city
FROM customers
WHERE city NOT LIKE 'C%';
-- Grace (city is NULL) is excluded

-- To include NULLs, handle them explicitly
SELECT first_name, city
FROM customers
WHERE city NOT LIKE 'C%' OR city IS NULL;
-- Grace is now included

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

Database	LIKE Case Behavior
PostgreSQL	Case-sensitive
SQLite	Case-sensitive for ASCII
MySQL	Case-insensitive (default collation)
SQL Server	Case-insensitive (default collation)

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:

name	price
Wireless Mouse	29.99

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:

name	price
Coffee Maker Pro	129.99

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

Output:

name	price
Data Science Handbook	42.50

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

Best Practice for Case-Insensitive Search

Always use LOWER() on both the column and the pattern when you want case-insensitive matching. This approach is portable across every database system.

-- Reliable pattern for case-insensitive search
WHERE LOWER(column_name) LIKE LOWER('%SearchTerm%')

-- Or simply write the pattern in lowercase
WHERE LOWER(column_name) LIKE '%searchterm%'

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:

name	price
Wireless Mouse	29.99

-- 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:

name
SQL for Beginners
Stainless Water Bottle

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

Output:

name
Yoga Mat Premium

NOT ILIKE

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

Output:

name
Wireless Mouse
Mechanical Keyboard
USB-C Hub
SQL for Beginners
Data Science Handbook
Coffee Maker Pro
Yoga Mat Premium
Running Shoes X1

ILIKE Is PostgreSQL Only

ILIKE does not exist in MySQL, SQL Server, or SQLite. If you use it, your query will only work in PostgreSQL.

-- PostgreSQL: Works
SELECT name FROM products WHERE name ILIKE '%mouse%';

-- MySQL: ERROR - ILIKE is not recognized
SELECT name FROM products WHERE name ILIKE '%mouse%';

-- Portable alternative: Works in ALL databases
SELECT name FROM products WHERE LOWER(name) LIKE '%mouse%';

Use ILIKE when you know your application will only ever run on PostgreSQL. Use LOWER() with LIKE when portability matters.

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:

name	price	is_available
Running Shoes X1	110.00	true

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:

name	price
Running Shoes X1	110.00

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:

name	price
Stainless Water Bottle	24.99
Wireless Mouse	29.99
SQL for Beginners	34.99
Data Science Handbook	42.50
USB-C Hub	45.00

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:

name	price
Stainless Water Bottle	24.99

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:

name	price	category
Data Science Handbook	42.50	Books

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:

name	price
Wireless Mouse	29.99
Bluetooth Speaker	65.00

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:

name	price
Yoga Mat Premium	38.00
Stainless Water Bottle	24.99

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:

name	price	category_id
Mechanical Keyboard	89.99	1
Yoga Mat Premium	38.00	4

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 '!'

Database Defaults

In MySQL and PostgreSQL, the backslash (\) is the default escape character, so you can often omit the ESCAPE clause:

-- MySQL / PostgreSQL: Backslash escaping works by default
WHERE name LIKE '%50\%%'

In SQLite and SQL Server, there is no default escape character, so you must always specify one with the ESCAPE clause.

For maximum portability, always include the ESCAPE clause explicitly.

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.

Pattern	Index Usable?	Performance
LIKE 'Wire%'	Yes	Fast
LIKE 'W%se'	Partially	Medium
LIKE '%Mouse'	No	Slow (full scan)
LIKE '%ouse%'	No	Slow (full scan)

For Large-Scale Text Search

If you need to search through millions of rows with %term% patterns frequently, consider using full-text search features instead of LIKE:

• PostgreSQL: tsvector and tsquery with full-text search indexes
• MySQL: FULLTEXT indexes with MATCH ... AGAINST
• External tools: Elasticsearch, Meilisearch, Algolia

These solutions are designed for text search and are orders of magnitude faster than LIKE '%term%' on large datasets.

Practical Exercises

Exercise 1

Find all products whose name starts with "M".

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

Expected output:

name	price
Mechanical Keyboard	89.99

Exercise 2

Find all customers whose email contains "bob".

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

Expected output:

first_name	last_name	email
Bob	Martinez	bob@email.com

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:

name	price
SQL for Beginners	34.99
Coffee Maker Pro	129.99
Bluetooth Speaker	65
Stainless Water Bottle	24.99

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:

first_name	last_name
Alice	Johnson
Carol	Singh
David	Chen
Frank	Wilson
Grace	Taylor

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:

name	price
Stainless Water Bottle	24.99
Yoga Mat Premium	38
Data Science Handbook	42.5
Bluetooth Speaker	65
Mechanical Keyboard	89.99
Coffee Maker Pro	129.99

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.