Comparing Text

To compare a column with a text, write the text in single quotes, as you did with || in the previous lesson:

SELECT title, price
FROM games
WHERE genre = 'Puzzle';

Typical mistake: text without quotes. SQLite then takes Puzzle for the name of a column, and WHERE genre = Puzzle ends with the error no such column: Puzzle.

= compares text exactly, character by character, and a capital letter is a different character than a lowercase one. WHERE genre = 'puzzle' finds nothing, and neither does 'Puzzle ' with a space at the end. The result of such a query is just the line with the column names, with no rows. When a query that should find something finds nothing, compare its text with the Tables tab.

The title Knight's Oath has an apostrophe in it, and that's the same character that ends a text. Here, the text ends after Knight, and SQLite doesn't know what to do with the rest:

SELECT *
FROM games
WHERE title = 'Knight's Oath';

Error on line 1: near "s": syntax error

Inside text, write an apostrophe twice: 'Knight''s Oath'. Two apostrophes in a row mean one apostrophe in the text, and the text goes on.

And double quotes? In SQL, they're for the names of tables and columns, not for text. SQLite sometimes takes "Puzzle" for a text too, but only when no column has that name, and other databases don't accept it at all. Text always goes in single quotes.

Text can be compared with <, >, <= and >= too. Then it goes by the alphabet: a text is “less” when it comes earlier in the alphabet. The games whose title comes before the letter D:

SELECT title
FROM games
WHERE title < 'D'
ORDER BY title;

Castle Siege and Cube Logic are there, even though they're longer than 'D': text is compared from the first character, and C comes before D. When one text is the beginning of another, the shorter one comes first, so 'Castle' is less than 'Castle Siege'.

Strictly speaking, SQLite compares text by the codes of its characters, and in them, all capital letters come before all lowercase ones: 'Zebra' < 'apple' is true. ORDER BY sorts text the same way, so a title starting with a lowercase letter would come after all the others.