LIKE operator

The LIKE operator in SQL matches string values against a pattern. It’s the standard way to do partial-string matching inside a WHERE clause, handling cases like names starting with Mo, emails containing @example.com, codes ending in -DRAFT.

The two wildcard characters:

• % — matches any sequence of characters (including the empty sequence).
• _ — matches exactly one character.

Examples:

SELECT * FROM employees WHERE first_name LIKE 'Mo%';     -- starts with Mo
SELECT * FROM employees WHERE first_name LIKE '%mo';     -- ends with mo
SELECT * FROM employees WHERE first_name LIKE '%mo%';    -- contains mo
SELECT * FROM employees WHERE first_name LIKE 'A__';     -- A followed by exactly two characters
SELECT * FROM employees WHERE first_name LIKE 'A___';    -- A followed by exactly three characters

So %mo matches Toshimo, Girolamo, Tomo, anyone whose name ends in those two letters. mo% matches Morgan, Mohammad, anyone starting with those letters.

Case sensitivity of LIKE is dialect-dependent. MySQL’s default collations are case-insensitive (_ci), so LIKE 'mo%' matches Mo, MO, mo. PostgreSQL is case-sensitive — use ILIKE for case-insensitive matching. SQLite is case-insensitive for ASCII characters under LIKE but case-sensitive for non-ASCII Unicode ('æ' LIKE 'Æ' is false). SQL Server depends on the column’s collation.

To match a literal % or _ character (rather than treat them as wildcards), use an escape character:

SELECT * FROM products WHERE name LIKE '50\%%' ESCAPE '\';

This matches names that literally start with 50% followed by anything.

For more sophisticated matching — regular expressions — most databases support REGEXP (MySQL) or ~ (PostgreSQL). But for everyday partial-string matching, LIKE is the right tool.