How do the LIKE wildcards % and _ work in SQL?

Think of a search box where % means "anything can go here" and _ means "exactly one letter goes here". It is like filling a crossword: _ is one empty square, % is a stretch of any length.

SELECT name FROM students WHERE name LIKE 'A%';      -- starts with A
SELECT name FROM students WHERE name LIKE '%an';     -- ends with an
SELECT name FROM students WHERE name LIKE '%ar%';    -- contains ar
SELECT name FROM students WHERE name LIKE '_a%';     -- second letter is a
SELECT name FROM students WHERE name LIKE '____';    -- exactly 4 letters

To search for a real percent sign, escape it:

SELECT * FROM offers WHERE label LIKE '50\%%' ESCAPE '\';

This matches labels beginning with 50%.

Case sensitivity depends on the database and collation. MySQL and SQL Server are usually case-insensitive by default; PostgreSQL LIKE is case-sensitive, and ILIKE is the case-insensitive version.

• Writing LIKE 'A*' with the shell-style star.
• Forgetting that LIKE 'abc' with no wildcard is just an equality check.