Android Question How to sort column with accented characters in SQLite database?

asales

Expert
Licensed User
Longtime User
I have this records in column City:
B4X:
Santo Domingo
São Paulo
Shanghai
Santiago
Sydney

When I use the query "SELECT City FROM Cities ORDER BY City", "São Paulo" is sorted incorrectly (the problem is the "Ã" accented character):
B4X:
Santiago
Santo Domingo
Shanghai
Sydney
São Paulo

How I can use a query or modify the database and sort the record with accented characters correctly?

Thanks in advance for any tip.
 

asales

Expert
Licensed User
Longtime User
Strange behavior:
if I have a name of city in uppercase the query shows correctly in Android 2.3, but not in 4.0 and 4.4.

Android 2.3.6:
B4X:
Santiago
Santo Domingo
São Paulo
-> SÃO PAULO
Shanghai
Sydney

Android and 4.4.4
B4X:
-> SÃO PAULO
Santiago
Santo Domingo
São Paulo
Shanghai
Sydney
 
Upvote 0

asales

Expert
Licensed User
Longtime User
Try:
B4X:
COLLATE NOCASE UNICODE
Don't worked:
B4X:
android.database.sqlite.SQLiteException: near "UNICODE": syntax error (code 1): , while compiling: SELECT City FROM Cities ORDER BY City COLLATE NOCASE UNICODE
 
Upvote 0

asales

Expert
Licensed User
Longtime User
You can use this code which will give you the order shown below:
B4X:
"SELECT City FROM Cities ORDER BY City COLLATE BINARY"
...
SÃO PAULO
São Paulo
Thanks but did not work.

See the example and images attached.

The "COLLATE UNICODE" works fine in Android 2.3 but not in 4.4.
The "COLLATE BINARY" don't sort the list correctly in both versions.

I tried "COLLATE UNICODE COLLATE NOCASE" and "COLLATED LOCALIZED", without success.
 

Attachments

  • db_city_list.zip
    8.4 KB · Views: 235
  • android_236.png
    android_236.png
    25.6 KB · Views: 355
  • android_444.png
    android_444.png
    58.7 KB · Views: 336
Upvote 0

KMatle

Expert
Licensed User
Longtime User
If you don't get it solved then create a 2nd culumn with a unified character set. Replace "Ã" with "A" (phonetic), convert all into lower case and do the sort to this additional culumn:

C1: SÃO PAULO C2: sao paulo

C2=Additional culumn for the sort
 
Upvote 0

Informatix

Expert
Licensed User
Longtime User
Strange behavior:
if I have a name of city in uppercase the query shows correctly in Android 2.3, but not in 4.0 and 4.4.

Android 2.3.6:
B4X:
Santiago
Santo Domingo
São Paulo
-> SÃO PAULO
Shanghai
Sydney

Android and 4.4.4
B4X:
-> SÃO PAULO
Santiago
Santo Domingo
São Paulo
Shanghai
Sydney
It is a known bug of Android since v4.0. It has been reported in 2012 and the case is still pending. I'm amazed by the number of bugs of Android... and the number of them still unfixed.
 
Upvote 0

Mahares

Expert
Licensed User
Longtime User
I think this code will do what you want to achieve. I tested it and it worked for me for the data you have:
B4X:
"Select City  FROM Cities ORDER BY (CASE WHEN SUBSTR(City,2,1)='Ã'  THEN REPLACE(City,'Ã','a') " _
& " WHEN  SUBSTR(City,2,1)='ã'  THEN REPLACE(City,'ã','a')  ELSE City END) COLLATE NOCASE"

This is what I get:
Santiago
Santo Domingo
SÃO PAULO
São Paulo
SAO PAULO
Shanghai
Singapore
Sydney

The more I learn, the more I realize how little I know!
 
Last edited:
Upvote 0
Top