Geek Answers Handbook

Problems with sqlite column ordering with accented characters (Á)

I have a sqlite database and I need to order a column that has several words starting with an accented character. These items are not ordered correctly, they appear at the end of the results:

Antonio Bonzo Zeto Angela

How can I dispose correctly when the sqlite database contains accents in this column?

thanks

+7
android sqlite
source share
2 answers

Or

... ORDER BY column COLLATE UNICODE

or

 ... ORDER BY column COLLATE LOCALIZED

Link:

In addition to the SQLite default BINARY collator, Android supplies two more, LOCALIZED , which changes with the current local system of the system and UNICODE , which is a Unicode sorting algorithm and is not adapted to the current language.

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
+18
source share

To support characters with an accent in the database, you can use the Normalizer class to save text correctly:

 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));

By storing data this way, statemants SQLite ASC and DESC work correctly with accented characters.

+1
source share

More articles:


All Articles