Lesson 10 of 61

5 mins read

SQL LIKE Operator & Wildcard Characters (%, _)

In Plain English

The `LIKE` operator performs pattern matching on string columns. It uses two universal wildcard characters: `%` (matches zero, one, or multiple characters) and `_` (matches exactly one single character).

The Mental Model

"`%` is like an accordion that stretches to fit any number of letters; `_` is a single fixed empty blank tile waiting for exactly one letter."

Deep Dive: How It Works

Wildcard Patterns: `'a%'` (starts with "a"), `'%a'` (ends with "a"), `'%or%'` (contains "or"), `'_r%'` (has "r" in second position), `'a_%_'` (starts with "a" and is at least 3 characters long).

Case Sensitivity: Standard SQL LIKE is case-sensitive; PostgreSQL provides `ILIKE` for case-insensitive matching; SQLite LIKE is case-insensitive for ASCII by default.

Escaping Wildcards: If searching for literal `%` or `_`, use an escape clause: `WHERE discount LIKE '50\%' ESCAPE '\'`.

Syntax Blueprint

SELECT column1
FROM table_name
WHERE column1 LIKE 'Zen%';    -- Starts with Zen

Use % for arbitrary length text matching and _ for exact single character positions.

Core Rules to Remember

Prefix Matching Performance: `LIKE 'prefix%'` can use B-Tree indexes efficiently; leading wildcards `LIKE '%suffix'` force full table scans.

NOT LIKE: Inverts the match to exclude specific patterns: `WHERE email NOT LIKE '%.test'`.

Single Character _: `WHERE code LIKE 'A_C'` matches ABC, A1C, but not ABBC.

Common Beginner Traps & How to Fix Them

Mistake:Using `LIKE '%query%'` everywhere and wondering why queries become slow on 10M rows.

Why it happens: Leading `%` prevents the database from using standard B-Tree index ordering.

How to fix: Use full-text search engines (FTS / PostgreSQL tsvector) or prefix matching when possible.

Pro Tip: In PostgreSQL, use `ILIKE` for case-insensitive pattern matching; in MySQL, use `LIKE BINARY` to enforce strict case sensitivity.

Live Interactive Example

Hit Run Code to see it live

Filtering Email Domains and Product Codes with LIKE

Python 3.12

Output Console

Click "Run Code" to view the rendered output.

How it works: % matched arbitrary domain prefixes, and ___ matched exactly 3 trailing characters.

Your Turn: Micro Challenge

No pressure! Edit the starter code below and test your solution with instant feedback.

Micro Exercise

Filter Names Starting with "J"

Select `name` from `contacts` where `name LIKE 'J%'`.

Sandbox Output

Click "Run & Check" to test your solution.

Finished reading and practicing?

Mark this lesson as completed to update your course progress.