LIKE & Wildcards (Pattern Matching)

= only finds exact matches, but real questions are fuzzier: "emails ending in @gmail.com", "products whose code starts with A", "names containing 'son'". LIKE with its two wildcards — % and _ — answers all of them.

Learn LIKE & Wildcards (Pattern Matching) in our free SQL course — a beginner-friendly interactive lesson with runnable queries, a practice exercise and a…

Part of the free SQL course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

You'll master starts-with, ends-with, and contains searches, the single-character wildcard, NOT LIKE , escaping literal wildcards, and why some patterns are fast while others crawl.

What You'll Learn

Our Sample Table: users

A handful of users with varied names and email domains — perfect for pattern matching.

1. The % Wildcard — Starts / Ends / Contains

The % wildcard stands for "any run of characters, including none". Its position decides the kind of search: 'a%' is starts-with, '%a' is ends-with, and '%a%' is contains.

% is the autocomplete dropdown. Typing "joh" and getting "John", "Johnson", "Johanna" is exactly WHERE name LIKE 'joh%' .

2. The _ Wildcard — Exactly One Character

The underscore _ matches precisely one character — no more, no less. It's perfect for fixed-format data like product codes, postal codes, or phone-number masks.

Your Turn: ends with "son"

Replace the placeholder with a pattern that matches every name ending in "son".

3. NOT LIKE & Escaping Wildcards

NOT LIKE flips the test — it returns rows that do not match. And when you need to search for a literal % or _ (not as wildcards), an ESCAPE character tells SQL to treat the next symbol as plain data.

🧩 Reorder Challenge

Unscramble these lines into a query that finds users whose email starts with "j" but is not a @shop.com address.

Why: SELECT and FROM come first, then a single WHERE holds both conditions joined by AND . The first LIKE anchors the start ('j%'); the second uses NOT LIKE to exclude the shop domain. In our data only "john@shop.com" starts with j — and it's excluded — so the result is empty, a useful reminder to check your assumptions.

🧠 Quick Recall

Predict each result, then reveal. Use the users table.

3 — Maria, Omar and Emma all have gmail addresses.

Yes — S, then exactly one char (a), then r, then anything (a Lin). The pattern fits "Sara Lin".

'A%' matches "A" (% allows zero chars), but 'A_' does not — the underscore demands one more character after A.

Common Errors (and the fix)

📘 Quick Reference

Pattern

Meaning

Frequently Asked Questions

Mini-Challenge: Gmail Users with "a"

Find Gmail users whose name contains the letter "a". Combine two LIKE conditions. Expected shape is in the comments.

🎉 Lesson Complete

Practice quiz

What does the % wildcard match in a LIKE pattern?

  • Exactly one character
  • Only digits
  • Any sequence of zero or more characters
  • Only letters

Answer: Any sequence of zero or more characters. % matches any run of characters, including none.

What does the _ wildcard match?

  • Exactly one character
  • Zero or more characters
  • A literal underscore only
  • A whole word

Answer: Exactly one character. _ matches precisely one character — no more, no less.

Which pattern finds values that START WITH 'a'?

  • '%a'
  • '%a%'
  • '_a'
  • 'a%'

Answer: 'a%'. The % at the end means 'a' followed by anything — a starts-with search.

Which pattern finds values that CONTAIN 'mid' anywhere?

  • 'mid%'
  • '%mid%'
  • '%mid'
  • 'mid'

Answer: '%mid%'. Wrapping the term in % on both sides gives a contains search.

For the value 'A', which is true?

  • LIKE 'A%' matches it but LIKE 'A_' does not
  • Both match it
  • Neither matches it
  • Only LIKE 'A_' matches it

Answer: LIKE 'A%' matches it but LIKE 'A_' does not. % allows zero characters so 'A%' matches 'A'; '_' demands one more character, so 'A_' does not.

What does NOT LIKE do?

  • Matches only exact strings
  • Negates the wildcards into literals
  • Returns rows that do NOT match the pattern
  • Is a syntax error

Answer: Returns rows that do NOT match the pattern. NOT LIKE returns the rows that fail the pattern.

How do you search for a LITERAL % sign in LIKE?

  • Wrap it in quotes twice
  • Use an ESCAPE character, e.g. LIKE '50\%%' ESCAPE '\'
  • Use _ instead
  • It is impossible

Answer: Use an ESCAPE character, e.g. LIKE '50\%%' ESCAPE '\'. An ESCAPE character tells SQL to treat the following % as data, not a wildcard.

Why is a leading-wildcard search like '%term' slow?

  • It returns too many rows
  • % is invalid at the start
  • It locks the table
  • It can't use a B-tree index, so it scans every row

Answer: It can't use a B-tree index, so it scans every row. A pattern starting with % can't use the sorted index; the database must scan the whole table.

Which LIKE pattern CAN use a normal index?

  • '%term' (leading wildcard)
  • 'term%' (anchored at the start)
  • '%term%'
  • '%t%m%'

Answer: 'term%' (anchored at the start). Patterns anchored at the start can use an index; leading wildcards cannot.

Does 'a_c' match the value 'abbc'?

  • Yes — _ matches any number of chars
  • Yes — it ignores length
  • No — _ is exactly one char, so 'a_c' needs 3 chars total
  • No — because it must start with b

Answer: No — _ is exactly one char, so 'a_c' needs 3 chars total. 'a_c' matches three-character strings like 'abc' or 'axc', not the four-character 'abbc'.

Continue this course

Related lessons