Android SQLiteDatabase query sorting order

Question

I have data in my database like this:

Alice
anderson
Beatrice
benny
Carmen
calzone

Using this code:

mDb.query(DATABASE_NAMES_TABLE, new String[] { KEY_ROWID,
            KEY_NAME }, null, null,
            null, null, KEY_NAME+ " ASC");

or this code with the filter being "":

mDb.query(true, DATABASE_NAMES_TABLE, new String[] { KEY_ROWID,
        KEY_NAME }, KEY_NAME + " LIKE ?",
        new String[] { filter+"%" }, null, null, null,
        null);

It sorts the above data like this:

Alice
Beatrice
Carmen
anderson
benny
calzone

Is this normal behavior? How do I get it to sort to the above order where all the As and Bs and Cs are together? Thanks.

score 12 · Answer 1 · answered Feb 01 '12 at 02:06

12

SQL uses ASCII location to sort so you'd use "COLLATE" in some fashion...

Something in the nature of:

ORDER BY YourColumn Collate NOCASE

or

ORDER BY YourColumn Collate SQL_Latin1_General_CP850_BIN

answered Feb 01 '12 at 02:06

kenyu73

671
6
16

Your answer is correct too but EboMike got to it first. :) Thanks though you were spot on! – Maurice Feb 01 '12 at 02:19

score 9 · Accepted Answer · answered Feb 01 '12 at 02:01

9

You can specify case sensitivity by adding COLLATE NOCASE, either in your ORDER BY clause, or (preferably) when you create the table itself in your declaration of that particular column.

answered Feb 01 '12 at 02:01

EboMike

76,846
14
164
167

score 1 · Answer 3 · answered Jan 13 '15 at 10:04

1

Cursor c = db.query(YOUR_TABLE_NAME, null, null, null, null, null, YOUR_COLOMN_NAME+" Collate NOCASE");

answered Jan 13 '15 at 10:04

user2786249

11
2

Can you explain a little bit more? – Jérémie Bertrand Jan 13 '15 at 10:25
in my case, i want to display 'name' from my 'form' table in order, so i used..--> Cursor c = db.query("form", null, null, null, null, null, "name Collate NOCASE"); And then ---> while (c.moveToNext()) { String studentname = c.getString(0).toString()} Note;- 'name' is my first colomn in database. – user2786249 Jan 16 '15 at 04:32

Android SQLiteDatabase query sorting order

3 Answers3

Linked