Patterns with LIKE

= compares a whole text. Sometimes you know only a part of it: the title starts with S, the email ends with a certain domain. That's what LIKE is for. It compares a text with a pattern:

SELECT title
FROM games
WHERE title LIKE 'S%'
ORDER BY title;

In a pattern, % stands for any text: any characters, as many as you like, or none at all. 'S%' means “S, and then anything”, so it finds the titles that start with S. '%s' finds the texts that end with s, and '%Pro%' those that have Pro anywhere in them: at the start, in the middle or at the end.

_ (an underscore) stands for exactly one character, whatever it is. The titles whose second letter is o:

SELECT title
FROM games
WHERE title LIKE '_o%'
ORDER BY title;

Unlike =, LIKE in SQLite doesn't mind capitals and lowercase: 's%' finds Shadow Ninja and Star Pilots too. Other databases may differ here; in PostgreSQL, for example, LIKE is case-sensitive.

NOT LIKE finds the texts that don't match the pattern. The reviews whose comment doesn't end with a period:

SELECT stars, comment
FROM reviews
WHERE comment NOT LIKE '%.'
ORDER BY stars DESC;

The reviews with no comment aren't in the result, even though a missing comment certainly doesn't end with a period. Their comment is NULL, and for NULL, SQLite can't tell whether it matches the pattern or not. More about that in the next exercise.

Typical mistake: % with = instead of LIKE. WHERE title = 'S%' finds nothing: = takes the % literally and looks for a title that is just an S and a percent sign.