LIKE & Pattern Matching
Search text with wildcard patterns — partial matches, prefixes, suffixes
Beginner 10 minLIKEwildcardspattern
Pattern Matching with LIKE
LIKE matches text against a pattern using two wildcard characters. It's how you search for partial matches, prefixes, suffixes, and patterns within text columns. Here's the syntax:
`%` matches any characters (zero or more) —
LIKE '%desk%' matches "Standing Desk", "Desk Lamp", or anything containing "desk" anywhere. LIKE 'A%' matches strings starting with A. LIKE '%son' matches strings ending with "son".`_` matches exactly one character —
LIKE 'J___' matches any 4-character string starting with J. Each underscore represents precisely one character position — useful for fixed-length pattern matching.Case sensitivity varies — LIKE is case-insensitive in SQLite, but only for ASCII letters. Non-ASCII characters like
é vs É or ü vs Ü are treated as different unless SQLite is built with the ICU extension. PostgreSQL is fully case-sensitive — use ILIKE for case-insensitive matching. MySQL's behavior depends on the column collation — _ci collations match case-insensitively, _cs/_bin don't. Always test on your target database.NOT LIKE excludes matches —
WHERE name NOT LIKE '%test%' filters out any rows containing "test" in the name column. Useful for cleaning out test data or excluding specific patterns from results.Knowledge Canvas
How LIKE Works
Pattern matching for strings
- % matches zero or more characters
- _ matches exactly one character
- LIKE case sensitivity varies: SQLite is case-insensitive for ASCII by default; PostgreSQL is case-sensitive (use ILIKE); MySQL depends on collation
- NOT LIKE excludes matching patterns
Pattern Cookbook
Common LIKE patterns
- Starts with:
LIKE 'A%'→ Alice, Aarav, Admin - Ends with:
LIKE '%@gmail.com' - Contains:
LIKE '%phone%'→ iPhone, Headphones - Exact length:
LIKE '___'→ 3-char strings only - Second char is 'a':
LIKE '_a%'
LIKE Pitfalls
Performance and behavior traps
- Leading %:
LIKE '%term'can't use indexes → full scan - Escape literal %:
LIKE '10\%'or use ESCAPE clause - Case sensitivity varies by database and collation
- LIKE with NULL → NULL (not TRUE or FALSE)
LIKE vs Other Matching
Alternatives to consider
LIKE: simple patterns
REGEXP/SIMILAR TO: complex patterns
% and _ wildcards
Full regex syntax
Portable across databases
Syntax varies by database
Syntax
Syntax Template
1WHERE column LIKE 'pattern';
2
3-- Wildcards
4-- % matches any characters (zero or more)
5-- _ matches exactly one character
6
7-- Examples
8-- WHERE name LIKE 'A%' → starts with A
9-- WHERE name LIKE '%son' → ends with son
10-- WHERE code LIKE 'J___' → 4 chars, starts with J
11
12-- Exclude matches
13-- WHERE name NOT LIKE '%test%'
%Matches zero or more characters_Matches exactly one characterNOT LIKEExcludes rows matching the pattern Sample Data
products
10 rows
| name | category |
|---|---|
| Wireless Mouse | Electronics |
| Office Desk | Furniture |
| LED Desk Lamp | Furniture |
| USB-C Cable | Electronics |
| Bluetooth Speaker | Electronics |
| Office Chair | Furniture |
| Coffee Maker | Appliances |
| Notebook Set | Stationery |
| Wireless Headphones | Electronics |
| Standing Desk | Furniture |
customers
12 rows
| id | name | city | country | |
|---|---|---|---|---|
| C1001 | Aarav Sharma | [email protected] | Mumbai | India |
| C1002 | Sara Chen | [email protected] | Singapore | Singapore |
| C1003 | James Wilson | [email protected] | London | UK |
| C1004 | Maria Garcia | [email protected] | Madrid | Spain |
| C1005 | Yuki Tanaka | [email protected] | Tokyo | Japan |
| C1006 | Priya Patel | [email protected] | Delhi | India |
| C1007 | Alex Johnson | [email protected] | New York | USA |
| C1008 | Chen Wei | [email protected] | Shanghai | China |
| C1009 | Emma Brown | [email protected] | Sydney | Australia |
| C1010 | Omar Hassan | [email protected] | Dubai | UAE |
| C1011 | Lena Muller | [email protected] | Berlin | Germany |
| C1012 | Ravi Kumar | [email protected] | Bangalore | India |
Worked Example
Find all products whose name contains 'Desk'.
SQL
1SELECT
2 name,
3 category
4FROM products
5WHERE name LIKE '%Desk%';
The % on both sides means 'Desk' can appear anywhere in the name — at the start, middle, or end. 'Standing Desk' and 'Desk Lamp' both match. 'Wireless Mouse' does not contain 'Desk' so it is excluded.
Output
2 rows
| name | category |
|---|---|
| Office Desk | Furniture |
| LED Desk Lamp | Furniture |
Key Concepts
1% = any characters (zero or more), _ = exactly one character
2LIKE case-folding is database-specific — SQLite folds only ASCII; PostgreSQL is case-sensitive (use ILIKE); MySQL depends on collation
3Searching for literal % or _ requires an ESCAPE clause
4'%keyword%' scans every row — can be slow on large tables
Pro Tip
Not all searches are exact matches. LIKE lets you find partial matches, prefixes, suffixes, and text that follows a structure.
When to Use
Searching by partial name, finding products with a keyword, matching email domains, validating text formats.
Find all customers whose email ends with '@email.com'. Show name and email, ordered by name.
Your Query