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

Answer 2

To support accented characters at database you can use the Normalizer class to store the text properly:

import java.text.Normalizer;

...

// Code to store the normalized data
ContentValues values = new ContentValues();
values.put(COLUMN_NAME, Normalizer.normalize(name, Normalizer.Form.NFD));

...

// Code to read the normalized data
int indexName = cursor.getColumnIndex(COLUMN_NAME)
String name = Normalizer.normalize(cursor.getString(indexName), Normalizer.Form.NFC));

Storing the data in this way, the SQLite statemants ASC and DESC works properly with accented characters.