CODEWITHSUNDEEP
androidsqlite
Maurice
Maurice

Reputation: 6433

Android SQLiteDatabase query sorting order

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.

Upvotes: 10

Views: 13859

Answers (3)

user2786249
user2786249

Reputation: 11

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

Upvotes: 1

kenyu73
kenyu73

Reputation: 681

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

Upvotes: 12

EboMike
EboMike

Reputation: 77752

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.

Upvotes: 9

Related Questions