Case insensitive sorting in SQLite

Written by Michael Lee on April 19, 2018

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
Thanks for taking the time to read this article. I'd love to stay in touch and share more tips on programming and design, side projects and working remotely. Sign up and I'll send you my articles straight to your email.

:wave: Hey hey, I'm Michael Lee! I really appreciate you taking the time to read this article.

Be sure to check out my other articles.