SQL Like

The LIKE operator searches for a pattern inside a text column instead of requiring an exact match. Instead of writing WHERE name = 'Alice', you can write WHERE name LIKE 'A%' to find every name that starts with the letter A. It is the standard SQL tool for partial-text matching — names, emails, addresses, product codes — and it works, with minor differences, across SQLite, MySQL, PostgreSQL, and SQL Server.

Overview: How LIKE Works Under the Hood

LIKE compares a column (or expression) against a pattern string that may contain two special wildcard characters: %, which matches zero or more of any character, and _, which matches exactly one character. Everything else in the pattern is matched literally, character by character. The database engine walks the pattern and the target string together: literal characters must match exactly, a _ consumes one character of input, and a % lets the engine skip ahead any number of characters before continuing to match the rest of the pattern.

Where LIKE sits in query processing matters. Like any other condition, a LIKE predicate in a WHERE clause is evaluated after the FROM clause has produced rows (including joins) and before GROUP BY, HAVING, SELECT, and ORDER BY run. A row is discarded before it ever reaches aggregation or column selection if it fails the LIKE test.

Performance depends heavily on where the wildcard sits. A pattern with no leading wildcard, like 'Sm%', can use a standard B-tree index the same way a range scan does, because the engine knows matching values must sort between Sm and Sn. A pattern that starts with a wildcard, like '%son', cannot use that index for narrowing and generally forces a full table or full index scan, since matching rows could start with any character. This is why starts-with searches stay cheap at scale while contains searches do not, unless the database has a specialized text or trigram index.

Case sensitivity also varies by engine. SQLite’s LIKE is case-insensitive for ASCII letters by default, so 'alice' matches 'ALICE'. PostgreSQL’s LIKE is case-sensitive by default — use ILIKE there for case-insensitive matching. MySQL’s behavior depends on the column’s collation, which is case-insensitive in most default collations. Always check your specific engine and collation rather than assuming behavior carries over.

Syntax

SELECT column_list
FROM table_name
WHERE column_name LIKE pattern [ESCAPE escape_character];

Part	Meaning
LIKE	Tests whether the column value matches the given pattern
NOT LIKE	Negation — matches values that do not fit the pattern
%	Wildcard matching zero or more of any character
_	Wildcard matching exactly one of any character
ESCAPE 'char'	Declares a character that, placed before % or _ in the pattern, makes them literal instead of wildcards

Patterns are always written as string literals in single quotes. A pattern with no % or _ at all behaves exactly like =.

Examples

Example 1: Starts-with matching

SELECT customer_name, city
FROM customers
WHERE customer_name LIKE 'A%'
ORDER BY customer_name;

Result:

customer_name	city
Alice Johnson	New York
Anna Martinez	Austin

The % after A matches any number of characters, so both Alice Johnson and Anna Martinez qualify because their names begin with the letter A. Bob Smith, Carla Ortiz, and David Lee are excluded because their first character is not A.

Example 2: Contains matching

SELECT customer_name, email
FROM customers
WHERE email LIKE '%gmail%'
ORDER BY customer_name;

Result:

customer_name	email
Alice Johnson	alice.j@gmail.com
Anna Martinez	anna.m@gmail.com
Carla Ortiz	carla_o@gmail.com

Putting % on both sides of gmail means the engine accepts any prefix and any suffix, so it finds gmail anywhere in the string. This is the classic contains search, and it is the pattern most likely to force a full table scan since there is no fixed starting point for an index to anchor on.

Example 3: Combining LIKE with other conditions

SELECT customer_name, city
FROM customers
WHERE city LIKE 'New%'
  AND customer_name LIKE 'A%';

Result:

customer_name	city
Alice Johnson	New York

Both New York and New Orleans match city LIKE 'New%', but only Alice Johnson’s name also starts with A, so David Lee (New Orleans) is filtered out by the second condition. This shows how LIKE combines with AND and OR just like any other boolean condition.

Example 4: The single-character wildcard and ESCAPE

SELECT customer_name
FROM customers
WHERE customer_name LIKE '_ob%';

Result:

customer_name
Bob Smith

The underscore matches exactly one character, so _ob% means: any single character, then literally ob, then anything. Only Bob Smith fits. If you need to search for a literal underscore or percent sign that is actually part of the data — such as the underscore in an email address — escape it:

SELECT customer_name, email
FROM customers
WHERE email LIKE '%carla\_o%' ESCAPE '\';

Result:

customer_name	email
Carla Ortiz	carla_o@gmail.com

The ESCAPE '\' clause tells the engine that a backslash before a wildcard character means match this character literally. Without it, the underscore in carla_o would still act as a wildcard matching any single character, which happens to work here but would also incorrectly match unintended strings like carlaXo.

How It Works Step by Step

For a query like SELECT customer_name FROM customers WHERE customer_name LIKE 'A%' ORDER BY customer_name;, the engine conceptually proceeds in this order:

1. FROM — build the working row set from customers (and resolve any joins, if present).
2. WHERE — evaluate customer_name LIKE 'A%' for every row; keep only rows where the pattern matches.
3. GROUP BY / HAVING — skipped here, since the query has no aggregation.
4. SELECT — project just the customer_name column from the surviving rows.
5. ORDER BY — sort the final result set alphabetically by customer_name.

Internally, the pattern-matching step itself behaves like a small state machine: the engine walks the pattern left to right, and for each literal character it must find an exact match at the current position; for _ it advances one position unconditionally; for % it tries matching the rest of the pattern at every possible remaining position until one succeeds or all fail. That is also why patterns with multiple % wildcards, or % combined with long strings, can be more expensive to evaluate than a simple prefix match.

Common Mistakes

Mistake 1: Forgetting to quote the pattern

SELECT customer_name
FROM customers
WHERE customer_name LIKE A%;

Without quotes, A% is not a valid string literal — the parser does not know what to do with the bare token A followed by a bare %, and raises a syntax error. Patterns must always be quoted strings:

SELECT customer_name
FROM customers
WHERE customer_name LIKE 'A%';

Mistake 2: Using filesystem-style wildcards

Many people coming from command-line tools instinctively reach for * and ?, but SQL’s LIKE does not use those — it uses % and _.

-- Wrong: '*' is treated as a literal character, not a wildcard
SELECT customer_name FROM customers WHERE customer_name LIKE 'A*';

This silently returns zero rows instead of raising an error, because no customer name actually contains the literal characters A followed by an asterisk — it is easy to miss this bug since there is no error message to flag it. The fix is to use SQL’s own wildcard syntax:

-- Correct: '%' matches any sequence of characters
SELECT customer_name FROM customers WHERE customer_name LIKE 'A%';

Best Practices

• Prefer a leading literal ('Sm%') over a leading wildcard ('%th') whenever possible — it lets the query planner use an index instead of scanning every row.
• Use ESCAPE whenever your search term might itself contain a literal % or _, such as searching for a percent sign or an email address with an underscore.
• Do not assume case sensitivity is the same across engines — test explicitly, or normalize both sides with UPPER() or LOWER() if you need guaranteed behavior.
• For heavy contains or fuzzy-text searches at scale, consider a dedicated full-text search index instead of LIKE '%term%', since it cannot use a normal index efficiently.
• Combine LIKE with other filters (AND/OR) to narrow the row set as early as possible, reducing how many rows need pattern matching.
• Prefer NOT LIKE over wrapping LIKE in NOT (...) for readability — both work, but NOT LIKE reads more naturally.

Practice Exercises

Using a customers table with columns customer_name, email, and city (as in the examples above):

1. Write a query that finds every customer whose city ends in the letter n (for example, Boston).
2. Write a query that finds every customer whose email address does not come from a gmail.com address.
3. Write a query that finds every customer whose name has a space followed by a word starting with the letter S (hint: think about how % and a literal space combine, e.g. '% S%').

Summary

• LIKE matches text against a pattern using % (any number of characters) and _ (exactly one character).
• NOT LIKE negates the match, returning rows that do not fit the pattern.
• Patterns with a leading wildcard ('%text') generally cannot use an index and force a full scan; leading-literal patterns ('text%') can.
• Use ESCAPE to search for a literal % or _ that is part of the actual data.
• Case sensitivity of LIKE varies by database engine — check before assuming.
• LIKE is evaluated in the WHERE clause, before GROUP BY, SELECT, and ORDER BY run.