android.database.sqlite.SQLiteException: near "UNICODE": syntax error (code 1): , while compiling: SELECT City FROM Cities ORDER BY City COLLATE NOCASE UNICODE
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:
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.
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!