android - Problems ordering sqlite by a column with accented characters (Á)

Question

I have a sqlite database, and i need to order by a column that haves some words starting by a accented character. These items are being ordered wrong, they are appearing on the end of the results:

Antonio Bonzo Zeto ángela

How can i order correctly when the sqlite database haves accents in that column?

thanks

Answer 1

Either

... ORDER BY column COLLATE UNICODE

or

... ORDER BY column COLLATE LOCALIZED

Reference:

In addition to SQLite's default BINARY collator, Android supplies two more, LOCALIZED, which changes with the system's current locale, and UNICODE, which is the Unicode Collation Algorithm and not tailored to the current locale.

Example:

db.execSQL("CREATE TABLE foo(a TEXT);");
db.execSQL("INSERT INTO foo VALUES('Antonio'),('Bonzo'),('Zeto'),('ángela');");

Cursor c = db.rawQuery("SELECT * FROM foo ORDER BY a COLLATE UNICODE", null);
while (c.moveToNext()) {
   Log.d("foo", c.getString(0));
}

Output:

ángela
Antonio
Bonzo
Zeto