160

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
CodeFusionMobile
  • 14,812
  • 25
  • 102
  • 140
  • 2
    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 Jan 31 '14 at 10:59

3 Answers3

278

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
Dharman
  • 30,962
  • 25
  • 85
  • 135
dan04
  • 87,747
  • 23
  • 163
  • 198
  • 11
    Is "ORDER BY TITLE COLLATE NOCASE" more efficient than "ORDER BY LOWER(TITLE)"? – Pascal Jan 31 '14 at 11:00
  • THAT SUCKS! isn't it? Wow! why the heck SQLite is making it case sensitive for sorting.... doesn't fit in my mind... unfortunately! – Vincy Feb 13 '18 at 11:18
  • 1
    @Vincy: I don't see what's so weird about case-sensitive string comparison. That's how the `<`, `==`, etc. operators work by default in every programming language that I'm familiar with. – dan04 Feb 13 '18 at 17:31
  • 1
    @dan04 how often you need a string that has to be case sensitive? that is why it is ridiculous. who in the world care about cases while sorting a string? The only place I see the advantage is for authentication or password validation! That is why be default it has to be CASE INSENSITIVE, imo! – Vincy Feb 14 '18 at 20:11
  • 2
    This usually won't work out of the box, if the database uses UTF8. (Notice that the title doesn't refer to just SQLite.) In this case, the suggestion to use lower() or upper() below is the one that works. – marco Aug 24 '18 at 10:45
  • it's make database reading speed too much lower than before , do you have an idea how to increase speed? – Yasin Hajilou Dec 15 '19 at 11:45
112

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;
Chad Birch
  • 73,098
  • 23
  • 151
  • 149
  • 1
    I'm sorting by multiple columns, do I have to put the LOWER around each one? – CodeFusionMobile Mar 09 '10 at 23:34
  • 2
    Yes, there's no way to change the behavior of ORDER BY to be case-insensitive. – Chad Birch Mar 09 '10 at 23:37
  • 2
    Is there any difference between 'UPPER' and 'LOWER' – Jagadeesh Apr 08 '13 at 15:38
  • 2
    For this purpose, no they will both achieve the same effect, which is to make the case of all items the same. – Ben Baron May 23 '13 at 02:13
  • 1
    They differ only for the characters [\]^_` . – dan04 May 27 '14 at 13:08
  • 1
    does not work for oracle sql when the selected set includes a union. – pete Jan 20 '15 at 21:03
  • @dan04 That would happen with ASCII order but I'm seeing ``VALUES ('lower'), ('['), (']'), ('^'), ('_'), ('`'), ('UPPER') ORDER BY upper(column1);`` as well as `BY lower(column1)` both return \` ^ _ [ ] lower UPPER order. (postgres 9.5, `SHOW lc_collate` outputs nothing, LC_COLLATE=en_US.utf8 env vars). https://wiki.postgresql.org/wiki/FAQ#Why_do_my_strings_sort_incorrectly.3F explains locale sorting normally compares letters before punctuation. – Beni Cherniavsky-Paskin Jul 14 '16 at 08:34
1
SELECT * FROM NOTES ORDER BY UPPER(title)
Sangram Shivankar
  • 3,535
  • 3
  • 26
  • 38
Md Shahriar
  • 2,072
  • 22
  • 11
  • 17
    Thanks for your answer. But: A **good answer** will always have an explanation of what was done and why it was done in such a manner, not only for the OP but for future visitors to SO. – B001ᛦ Aug 31 '16 at 10:47