How to use SQL Order By statement to sort results case insensitive?

150
votes

I have a SQLite database that I am trying to sort by Alphabetical order. The problem is, SQLite doesn't seem to consider A=a during sorting, thus I get results like this:

A B C T a b c g

I want to get:

A a b B C c g T

What special SQL thing needs to be done that I don't know about?

SELECT * FROM NOTES ORDER BY title
3
sqlsqlitesortingsql-order-by
And what is the more efficient way to do it? "ORDER BY TITLE COLLATE NOCASE" or "ORDER BY LOWER(TITLE)". (FYI, in my case, running on Android, i.e. SQLite) - Pascal

3 Answers

263
votes

You can also do ORDER BY TITLE COLLATE NOCASE.

Edit: If you need to specify ASC or DESC, add this after NOCASE like

ORDER BY TITLE COLLATE NOCASE ASC

or

ORDER BY TITLE COLLATE NOCASE DESC
104
votes

You can just convert everything to lowercase for the purposes of sorting:

SELECT * FROM NOTES ORDER BY LOWER(title);

If you want to make sure that the uppercase ones still end up ahead of the lowercase ones, just add that as a secondary sort:

SELECT * FROM NOTES ORDER BY LOWER(title), title;
1
votes
SELECT * FROM NOTES ORDER BY UPPER(title)