Case insensitive sorting in SQLite

In SQLite you can retrieve rows from a table in either ascending or descending order.

Ascending:

SELECT name FROM table_name ORDER BY name ASC;

Descending:

SELECT name FROM table_name ORDER BY name DESC;

But by default SQLite will return the capitalized entries first or last depending on the direction you’re sorting by. This is of course if you’ve got data you’re trying to sort by with mixed casing.

ACID
ACL
BRB
.
.
.
a11y

In the case of my project, Spell It Out, the titles for acronyms were made up of both uppercase and lowercase acronyms. For this reason, I wanted the ordering to be case insensitive.

It turns out SQLite provides a collating function called NOCASE for this exact kind of sorting.

So whether the acronym started with A or a, they would end up in the same group of acronyms instead of one coming after the other.

The new query for case insensitive sorting is now:

Ascending:

SELECT name FROM table_name ORDER BY name COLLATE NOCASE ASC;

Descending:

SELECT name FROM table_name ORDER BY name COLLATE NOCASE DESC;

Which should result in:

a11y
ACID
ACL
BRB