LIKE, GLOB, and Pattern Matching

~12 min · sql, like, glob, patterns

Level 0Scout

0 XP0/80 lessons0/10 achievements

0/120 XP to next level120 XP to go0% complete

Substring search done right

SQLite gives you three pattern operators:

LIKE 'pat%' — SQL standard. % = any string, _ = single char. Case-insensitive for ASCII A–Z by default; Unicode-case-sensitive unless you load ICU.
GLOB 'pat*' — Unix-style. * = any string, ? = single char, [abc] = char class. Always case-sensitive. Faster than LIKE for prefix matches.
REGEXP 'pat' — only available if your build loads a regex extension (Pippa's does via sqlite3_extension_init; the default macOS/Linux CLI does not).

Tip: For real text search — substring across many rows, ranking, language tokenization — none of these are the right tool. You want FTS5 (covered in track 8). LIKE is fine for WHERE name LIKE 'al%' on a small table; it falls over at scale.

Code

LIKE patterns·sql

SELECT * FROM users WHERE username LIKE 'al%';      -- starts with 'al'
SELECT * FROM users WHERE email    LIKE '%@gmail.com'; -- ends with
SELECT * FROM users WHERE username LIKE 'a_ice';      -- 'alice' or 'arice'
SELECT * FROM users WHERE email NOT LIKE '%@%';        -- bad email shape

-- LIKE is case-insensitive for ASCII A-Z by default
SELECT * FROM users WHERE username LIKE 'ALICE';      -- matches 'alice'

GLOB — Unix-style and case-sensitive·sql

SELECT * FROM files WHERE path GLOB '*.{md,txt}'; -- not supported, glob has no brace expansion
SELECT * FROM files WHERE path GLOB '*.py';       -- ends in .py
SELECT * FROM files WHERE path GLOB '[A-Z]*';     -- starts with uppercase

External links

Exercise

On a 10,000-row table with a TEXT column, time three queries: (a) LIKE 'al%' with no index, (b) the same with CREATE INDEX ON t(col), (c) LIKE '%al%' with the index. Note which queries the index helps and which it cannot. Then write a one-paragraph note about when LIKE stops being the right tool.

LIKE, GLOB, and Pattern Matching

Substring search done right

Code

External links

Exercise

Progress

Comments 0