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

asales

Well-Known Member
Licensed 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

Well-Known Member
Licensed 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
 

asales

Well-Known Member
Licensed 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
 

Mahares

Well Known Member
Licensed User
You can use this code which will give you the order shown below:
B4X:
"SELECT City FROM Cities ORDER BY City COLLATE BINARY"
Santiago
Santo Domingo
Shanghai
Sydney
SÃO PAULO
São Paulo
 

asales

Well-Known Member
Licensed 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

KMatle

Expert
Licensed 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
 

Informatix

Expert
Licensed 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.
 

Mahares

Well Known Member
Licensed 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:
Top