Python SQL LIKE Operator & Wildcard Characters (%, _)
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).
"`%` 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
Common Beginner Traps & How to Fix Them
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.
Live Interactive Example
Hit Run Code to see it liveYour Turn: Micro Challenge
No pressure! Edit the starter code below and test your solution with instant feedback.
Filter Names Starting with "J"
- Select `name` from `contacts` where `name LIKE 'J%'`.
Finished reading and practicing?
Mark this lesson as completed to update your course progress.