Pattern Matching with LIKE

Explanation

So far, WHERE clauses have matched exact values. But often you don't know the exact value, you just know part of it. Maybe you want every movie whose title starts with "The," or every title that contains the word "Star." That's exactly what the LIKE operator is for: matching text against a pattern instead of an exact string.

LIKE uses two special wildcard characters:

Syntax

SELECT column_name
FROM table_name
WHERE column_name LIKE 'pattern';

A few common patterns:

-- Starts with "Star"
WHERE title LIKE 'Star%'
 
-- Ends with "Story"
WHERE title LIKE '%Story'
 
-- Contains "man" anywhere
WHERE title LIKE '%man%'
 
-- Exactly 4 characters, starting with "T"
WHERE title LIKE 'T___'

You can also negate a pattern with NOT LIKE to exclude matching rows.

Interactive Example

Common Mistakes

• Forgetting the wildcard entirely. WHERE title LIKE 'Star' only matches the exact text "Star," it behaves just like =. You almost always want at least one %.
• Using * instead of %. SQL's wildcard for "any characters" is %, not the * you might be used to from file searches.
• Assuming case sensitivity works the same everywhere. Behavior differs across database engines, so test your assumption rather than guessing.

Best Practices

• Put % at the start, end, or both, depending on whether you're matching a suffix, prefix, or substring.
• Use _ sparingly, and only when you actually need to match a specific number of characters.
• If performance matters on large tables, remember that a leading % (like '%man%') usually can't use an index efficiently, since the database has to scan the whole text.

Practice Question

Using the playground above, write a query that finds every movie in the movies table whose title ends with the letter "r".

Summary

LIKE lets you search for text patterns instead of exact matches, using % for any sequence of characters and _ for a single character. It's one of the most useful tools for flexible, human-friendly text searches in SQL.

FAQ