LIKE Operator
SQL · Filtering Rows
Theory
LIKE matches text against a pattern with two wildcards: % stands for any number of characters (including none) and _ stands for exactly one character. name LIKE 'A%' finds names starting with A, '%a' finds names ending in a, '%ee%' finds names containing ee, and '_a%' finds names whose second letter is a.
Case sensitivity depends on the engine and its collation: PostgreSQL LIKE is case-sensitive (it adds ILIKE for case-insensitive), while MySQL, SQL Server and SQLite are usually case-insensitive for plain letters. A pattern with a leading % cannot use a normal index, so it scans the whole table. To search for a literal % or _, use an ESCAPE character, e.g. LIKE '50\%%' ESCAPE '\'. NOT LIKE keeps rows that do not match.
Why we use it
Text data is messy and people often know only part of a value: a name prefix, a domain in an email, a code fragment. LIKE finds these partial matches without needing exact values.
When to use it
Use LIKE for simple prefix, suffix or contains searches. For exact values use = or IN. For complex patterns use regular expression functions, and for large-scale text search use full-text search features.
How it works
- Read a row
- Take the text value
- Walk the pattern: % = any run of chars, _ = exactly one char
- Does the whole value fit the pattern? Yes: Keep the row. No: Discard the row.
- Move to the next row
Syntax
SELECT columns FROM table
WHERE column LIKE 'pattern';Example
SELECT * FROM employees WHERE name LIKE 'A%';Key points
- % matches any number of characters, including zero
- _ matches exactly one character
- Case sensitivity depends on the engine (PostgreSQL has ILIKE)
- A leading % cannot use a normal index
- Use ESCAPE to search for a literal % or _