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.

回答1:

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.

回答2:

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

回答3:

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