21

just wondering if theres a way of sorting the results of a query ignoring case. Cause at the moment fields starting with an upper case letter are sorted and put on top of the list and fields starting with lower case letter are also sorted but are put after the upper case fields.

Thanks -- Mike

mixkat
  • 3,883
  • 10
  • 40
  • 58

3 Answers3

52

ORDER BY column COLLATE NOCASE;

See http://www.sqlite.org/datatype3.html#collation

aaz
  • 5,136
  • 22
  • 18
  • 3
    I tried doing this and was getting the error `SQLiteException: near "COLLATE": syntax error: , while compiling: SELECT * FROM table ORDER BY column ASC COLLATE NOCASE`. The solution is to remove the `ASC` and only specify the direction when it is `DESC`. – plowman Jul 28 '11 at 22:57
  • 9
    @unchek — Also try [`COLLATE NOCASE ASC`](http://www.sqlite.org/syntaxdiagrams.html#ordering-term). – aaz Jul 30 '11 at 09:23
  • 2
    @plowman Just like aaz said, the ASC or DESC should be after the NOCASE. – Surya Wijaya Madjid May 29 '12 at 15:24
1

On the Android you are given a collation function called LOCALIZED.

When you specify your column, do the following:

CREATE TABLE song(..., title TEXT COLLATE LOCALIZED ASC, ...)

It works great. If you have the Android source code, just do a search for "COLLATE" to see all sorts of examples.

djunod
  • 4,876
  • 2
  • 34
  • 28
0

Convert all entries to lower case for example: select name from table order by LCASE(name)

LCASE or LOWER (Check documentation)

Pepe
  • 6,360
  • 5
  • 27
  • 29