Functions in WHERE and ORDER BY

Functions aren't only for SELECT. You can use them wherever a value can be, so in WHERE and ORDER BY too. Which customers joined in 2024?

SELECT name, joined
FROM customers
WHERE strftime('%Y', joined) = '2024';

For every row, SQLite calculates the function and then checks the condition. Here, LIKE '2024-%' would find the same customers, but with functions, you can ask about any part of a value, and the condition says clearly what it's about: strftime('%m', joined) = '03' finds the customers who joined in March of any year, and LENGTH(name) > 11 those with long names.

Typical mistake: comparing the result of strftime with a number. WHERE strftime('%Y', joined) = 2024 finds nothing, and it doesn't give an error either. strftime gives a text, '2024', and for SQLite, the text '2024' isn't equal to the number 2024: they're different kinds of values. So compare it with a text in quotes. The same goes for SUBSTR, whose result is a text too.

Functions also help to compare texts whose case you don't know: WHERE LOWER(genre) = 'rpg' finds RPG games however the genre is written.

In ORDER BY, a function sorts the rows by a calculated value. Here, from the shortest name:

SELECT name, LENGTH(name) AS characters
FROM customers
ORDER BY LENGTH(name), name
LIMIT 3;

As you know, ORDER BY can also use the alias of a column: ORDER BY characters, name sorts the same way. In WHERE, most databases don't allow aliases, because they check the conditions before they calculate the columns of the result. SQLite is lenient and allows them, but in WHERE, it's better to write the whole function again, so that your queries work in other databases too.