LIKE & Wildcards (Pattern Matching)

Reviewed & published by Brayan K

= 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.

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

• The % wildcard (zero or more chars)
• The _ wildcard (exactly one char)
• Starts-with, ends-with, contains patterns
• NOT LIKE to exclude matches
• Escaping a literal % or _
• Why '%term' searches are slow

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%'.

-- The three tables this lesson uses. Run this block first;
-- every later block queries them.
CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    name  TEXT,
    email TEXT
);
INSERT INTO users VALUES
    (1, 'Aisha Khan',     'aisha@shop.com'),
    (2, 'Andre Silva',    'andre@mail.io'),
    (3, 'Maria Garcia',   'maria@gmail.com'),
    (4, 'Omar Aziz',      'omar@shop.com'),
    (5, 'Sara Lin',       'sara@gmail.com'),
    (6, 'Ben Johnson',    'ben@mail.io'),
    (7, 'Priya Anderson', 'priya@shop.com'),
    (8, 'Jonas Meyer',    'jonas@mail.io'),
    (9, 'Jade Wu',        'jade@shop.com');

CREATE TABLE products (id INTEGER PRIMARY KEY, code TEXT);
INSERT INTO products VALUES
    (1, 'A19'), (2, 'AX9'), (3, 'A9'), (4, 'AB19'),
    (5, '50%OFF'), (6, '50%-A'), (7, 'B19');

CREATE TABLE courses (id INTEGER PRIMARY KEY, title TEXT);
INSERT INTO courses VALUES
    (1, 'PSQL for Beginners'),
    (2, 'TSQL Window Functions'),
    (3, 'SQL Fundamentals'),
    (4, 'Advanced SQL Joins');

-- % matches any number of characters (including none)
SELECT name, email
FROM users
WHERE email LIKE 'a%';   -- email STARTS WITH 'a'

-- ✅ Expected result:
-- name | email
-- Aisha Khan | aisha@shop.com
-- Andre Silva | andre@mail.io

-- Uses the users table from the first example.
-- Put the % at the FRONT and the pattern becomes "ends with"
SELECT name, email
FROM users
WHERE email LIKE '%@shop.com';   -- email ENDS WITH @shop.com

-- ✅ Expected result:
-- name | email
-- Aisha Khan | aisha@shop.com
-- Omar Aziz | omar@shop.com
-- Priya Anderson | priya@shop.com
-- Jade Wu | jade@shop.com

-- Uses the users table from the first example.
-- Wrap the term in % on BOTH sides to find it ANYWHERE in the value
SELECT name
FROM users
WHERE name LIKE '%ar%';   -- contains "ar" anywhere

-- ✅ Expected result:
-- name
-- Maria Garcia
-- Omar Aziz
-- Sara Lin

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.

-- Uses the products table from the first example.
-- _ matches EXACTLY ONE character
SELECT code
FROM products
WHERE code LIKE 'A_9';   -- A, then any 1 char, then 9

-- A9 is too short and AB19 is too long, so neither comes back.

-- ✅ Expected result:
-- code
-- A19
-- AX9

-- Uses the courses table from the first example.
-- Combine them: first char anything, then 'SQL', then anything
SELECT title
FROM courses
WHERE title LIKE '_SQL%';

-- 'SQL Fundamentals' does NOT match: _ demands a character BEFORE the S.

-- ✅ Expected result:
-- title
-- PSQL for Beginners
-- TSQL Window Functions

Your Turn: ends with "son"

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

-- 🎯 YOUR TURN — fill in the pattern, then "Try it Yourself"
-- Uses the users table from the first example.
-- Goal: find every user whose name ENDS with "son" (Johnson, Anderson...).

SELECT name
FROM users
WHERE name LIKE ___;   -- 👉 replace ___ with '%son'

-- ✅ Expected result:
-- name
-- Ben Johnson
-- Priya Anderson

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.

-- Uses the users table from the first example.
-- NOT LIKE returns rows that DON'T match the pattern
SELECT name, email
FROM users
WHERE email NOT LIKE '%@shop.com';   -- everyone NOT on @shop.com

-- ✅ Expected result:
-- name | email
-- Andre Silva | andre@mail.io
-- Maria Garcia | maria@gmail.com
-- Sara Lin | sara@gmail.com
-- Ben Johnson | ben@mail.io
-- Jonas Meyer | jonas@mail.io

-- Uses the products table from the first example.
-- Searching for a LITERAL % sign? Escape it.
SELECT code
FROM products
WHERE code LIKE '50\%%' ESCAPE '\';

-- The first \%  = a literal percent sign
-- The trailing % = the wildcard "anything after"

-- ✅ Expected result:
-- code
-- 50%OFF
-- 50%-A

🧩 Reorder Challenge

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

-- SCRAMBLED — arrange into a query that finds users whose
-- email starts with "j" but is NOT a @shop.com address.

WHERE email LIKE 'j%'
SELECT name, email
AND email NOT LIKE '%@shop.com'
FROM users

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)

• Forgetting the wildcard: LIKE 'shop.com' matches only the exact string. For contains, you need LIKE '%shop.com%'.
• Case surprises: LIKE 'a%' may miss "Aisha" in case-sensitive databases. Use ILIKE (Postgres) or LOWER(name) LIKE 'a%'.
• Unescaped literal wildcard: searching for "100%" with LIKE '100%' matches anything starting with 100. Escape it: LIKE '100\%' ESCAPE '\'.
• Leading-wildcard performance: LIKE '%term' can't use an index and scans the whole table. Avoid it in hot paths; use full-text search instead.

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.

-- 🎯 MINI-CHALLENGE — uses only this lesson's ideas
-- Marketing wants every Gmail user whose name contains the letter "a".
--   1. email ends with "@gmail.com"
--   2. AND name contains "a" anywhere
--
-- ✅ Expected: rows matching BOTH conditions.

-- your query here

🎉 Lesson Complete

• ✅ % matches zero or more characters; position sets the search type
• ✅ _ matches exactly one character
• ✅ NOT LIKE returns the rows that don't match
• ✅ Use ESCAPE to search for a literal % or _
• ✅ Leading-wildcard patterns can't use an index and are slow
• ✅ Next: match lists and ranges with IN and BETWEEN

Practice quiz

Continue this course

• Previous: Column & Table Aliases (AS)
• Next: IN, BETWEEN & Logical Operators — Filter with IN, BETWEEN, AND, OR and NOT
• Quick reference: SQL cheat sheet › Filtering & Operators

Frequently asked questions

What's the difference between % and _ ?

% matches any sequence of zero or more characters, while _ matches exactly one character. 'a%' finds anything starting with a; 'a_c' finds three-letter strings like abc or axc but not ac or abbc.

Is LIKE case-sensitive?

It depends on the database and column collation. PostgreSQL's LIKE is case-sensitive (use ILIKE for case-insensitive); MySQL's default collation is case-insensitive; SQLite's LIKE is case-insensitive for ASCII by default. When in doubt, force it with LOWER(col) LIKE LOWER('...').

How do I search for a literal % or _ ?

Use an ESCAPE character. For example: WHERE code LIKE '50\%%' ESCAPE '\' finds codes starting with the literal text '50%'. The backslash tells SQL the next % is data, not a wildcard.

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

A pattern starting with % can't use a normal B-tree index, so the database must scan every row. Patterns anchored at the start ('term%') can use an index. For frequent contains-style search, use full-text search instead.

When should I use LIKE vs full-text search or regex?

LIKE is perfect for simple prefix/suffix/contains checks. For ranking, stemming, or natural-language search across large text, use the database's full-text engine. For complex patterns, many databases offer regex operators (~ in Postgres, REGEXP in MySQL).

Related lessons

• WHERE Clause