SQL LIKE Operator: Pattern Matching with % and _ (Examples)

SQL Tutorial · Chapter 9 of 32

The SQL LIKE operator searches text columns for a pattern instead of an exact value. It is used in a WHERE clause together with two wildcard characters: % stands for any sequence of characters (including none) and _ stands for exactly one character. LIKE is how you find names starting with A, emails ending in .com or products containing “top”.

SQL LIKE Syntax

SELECT columns
FROM table_name
WHERE column LIKE 'pattern';

-- exclude matches
WHERE column NOT LIKE 'pattern';

Sample Tables

Example 1: Starts With

SELECT Name, City
FROM Customers
WHERE City LIKE 'M%';

Example 2: Ends With

SELECT Name
FROM Customers
WHERE Name LIKE '%a';

Example 3: Contains

Wildcards on both sides find the text anywhere in the value. Note that this form cannot use a normal index, so it is slower on very large tables.

SELECT OrderID, Product
FROM Orders
WHERE Product LIKE '%o%';

Example 4: Single-Character Wildcard

SELECT Product
FROM Orders
WHERE Product LIKE 'M_____';

Six characters are required: M plus five underscores. Mouse has only five letters, so it is not matched.

Example 5: NOT LIKE

SELECT Name, Country
FROM Customers
WHERE Country NOT LIKE '%a%';

Every other country contains the letter a (in Singapore and Australia it is lower case; see the case-sensitivity note below).

Works in MySQL, SQL Server, PostgreSQL, SQLite

• Case sensitivity: LIKE ignores case in MySQL, SQL Server and SQLite (for ASCII letters) but is case sensitive in PostgreSQL. PostgreSQL offers ILIKE for case-insensitive matching; elsewhere use LOWER(column) LIKE 'a%'.
• Extra wildcards: SQL Server adds [abc] and [^abc] character sets. The others do not; use regular expressions instead.
• Escaping: to match a literal % or _, all four support LIKE '50\%' ESCAPE '\' (MySQL uses backslash by default).

When to Use LIKE and When Not To

LIKE is the right tool when you know part of a value: a surname prefix, a product code family, an email domain. It is the wrong tool when you know the exact value; WHERE Country = 'India' is clearer and faster than LIKE 'India' and can use an index directly. LIKE is also not a substitute for a numeric or date filter. Searching OrderDate LIKE '2026-01%' works on text-stored dates in SQLite but fails or converts silently elsewhere; use a date range instead.

Performance follows a simple rule: a pattern that starts with fixed characters ('Lap%') can use a normal index, because the database jumps to the first matching entry. A pattern that starts with a wildcard ('%top' or '%o%') cannot, so the database reads every row. On tables with millions of rows this is the difference between milliseconds and seconds. If you need fast contains-searches, look at full-text indexes (MySQL FULLTEXT, SQL Server CONTAINS, PostgreSQL tsvector).

Common Mistakes

• Forgetting the wildcard: LIKE 'Laptop' is just an exact match.
• Using * or ? from file systems; SQL uses % and _.
• Applying LIKE to a numeric column; convert it to text first or use comparison operators.
• Leading wildcards on huge tables without a full-text index, causing slow scans.

Practice Exercise

1. Find customers whose Name contains “an” in any position (case-insensitive).
2. Find products that are exactly five characters long.

-- 1
SELECT Name FROM Customers WHERE LOWER(Name) LIKE '%an%';
-- Anita Sharma

-- 2
SELECT DISTINCT Product FROM Orders WHERE Product LIKE '_____';
-- Mouse

Related Chapters

• SQL Wildcards – every wildcard character in detail
• SQL WHERE – where LIKE is used
• SQL IN – matching a list of exact values
• SQL from Scratch: full course overview

FAQ

What is the difference between LIKE and = in SQL?

= requires an exact match of the whole value. LIKE compares against a pattern, so with wildcards it can match the beginning, end or middle of a value.

Is SQL LIKE case sensitive?

It depends on the database. PostgreSQL is case sensitive (use ILIKE); MySQL, SQL Server and SQLite are not by default for standard collations.

How do I search for a literal percent sign with LIKE?

Use an escape character: WHERE Discount LIKE ‘%50\%%’ ESCAPE ‘\’ matches values containing “50%”.

Chapter 9 of 32 · SQL from Scratch: all 32 chapters