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

[asales]

I have this records in column City:

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

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.

 

[imbault]

try :

"SELECT City FROM Cities ORDER BY City COLLATE UNICODE"

 

[asales]

This works. Thanks.

 

[asales]

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:

Santiago
Santo Domingo
São Paulo
-> SÃO PAULO
Shanghai
Sydney

Android and 4.4.4

-> SÃO PAULO
Santiago
Santo Domingo
São Paulo
Shanghai
Sydney

 

[NJDude]

Try:

COLLATE NOCASE UNICODE

 

[asales]

Try:

COLLATE NOCASE UNICODE

Don't worked:

android.database.sqlite.SQLiteException: near "UNICODE": syntax error (code 1): , while compiling: SELECT City FROM Cities ORDER BY City COLLATE NOCASE UNICODE

 

[Mahares]

You can use this code which will give you the order shown below:

"SELECT City FROM Cities ORDER BY City COLLATE BINARY"

Santiago
Santo Domingo
Shanghai
Sydney
SÃO PAULO
São Paulo

 

[asales]

You can use this code which will give you the order shown below:

"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.

 

[KMatle]

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]

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:

Santiago
Santo Domingo
São Paulo
-> SÃO PAULO
Shanghai
Sydney

Android and 4.4.4

-> 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]

I think this code will do what you want to achieve. I tested it and it worked for me for the data you have:

"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!