asales Expert Licensed User Longtime User Jan 25, 2015 #1 I have this records in column City: Code: 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): Code: 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.
I have this records in column City: Code: 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): Code: 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 Well-Known Member Licensed User Longtime User Jan 25, 2015 #2 try : Code: "SELECT City FROM Cities ORDER BY City COLLATE UNICODE" Upvote 0
asales Expert Licensed User Longtime User Jan 25, 2015 #4 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: Code: Santiago Santo Domingo São Paulo -> SÃO PAULO Shanghai Sydney Android and 4.4.4 Code: -> SÃO PAULO Santiago Santo Domingo São Paulo Shanghai Sydney Upvote 0
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: Code: Santiago Santo Domingo São Paulo -> SÃO PAULO Shanghai Sydney Android and 4.4.4 Code: -> SÃO PAULO Santiago Santo Domingo São Paulo Shanghai Sydney
asales Expert Licensed User Longtime User Jan 26, 2015 #6 NJDude said: Try: Code: COLLATE NOCASE UNICODE Click to expand... Don't worked: Code: android.database.sqlite.SQLiteException: near "UNICODE": syntax error (code 1): , while compiling: SELECT City FROM Cities ORDER BY City COLLATE NOCASE UNICODE Upvote 0
NJDude said: Try: Code: COLLATE NOCASE UNICODE Click to expand... Don't worked: Code: android.database.sqlite.SQLiteException: near "UNICODE": syntax error (code 1): , while compiling: SELECT City FROM Cities ORDER BY City COLLATE NOCASE UNICODE
M Mahares Expert Licensed User Longtime User Jan 26, 2015 #7 You can use this code which will give you the order shown below: Code: "SELECT City FROM Cities ORDER BY City COLLATE BINARY" Santiago Santo Domingo Shanghai Sydney SÃO PAULO São Paulo Upvote 0
You can use this code which will give you the order shown below: Code: "SELECT City FROM Cities ORDER BY City COLLATE BINARY" Santiago Santo Domingo Shanghai Sydney SÃO PAULO São Paulo
asales Expert Licensed User Longtime User Jan 26, 2015 #8 Mahares said: You can use this code which will give you the order shown below: Code: "SELECT City FROM Cities ORDER BY City COLLATE BINARY" ... SÃO PAULO São Paulo Click to expand... 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 db_city_list.zip 8.4 KB · Views: 514 android_236.png 25.6 KB · Views: 707 android_444.png 58.7 KB · Views: 716 Upvote 0
Mahares said: You can use this code which will give you the order shown below: Code: "SELECT City FROM Cities ORDER BY City COLLATE BINARY" ... SÃO PAULO São Paulo Click to expand... 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 Expert Licensed User Longtime User Jan 26, 2015 #9 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
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 Longtime User Jan 26, 2015 #10 AS2 said: 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: Code: Santiago Santo Domingo São Paulo -> SÃO PAULO Shanghai Sydney Android and 4.4.4 Code: -> SÃO PAULO Santiago Santo Domingo São Paulo Shanghai Sydney Click to expand... 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
AS2 said: 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: Code: Santiago Santo Domingo São Paulo -> SÃO PAULO Shanghai Sydney Android and 4.4.4 Code: -> SÃO PAULO Santiago Santo Domingo São Paulo Shanghai Sydney Click to expand... 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.
M Mahares Expert Licensed User Longtime User Jan 26, 2015 #11 I think this code will do what you want to achieve. I tested it and it worked for me for the data you have: Code: "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: Jan 26, 2015 Upvote 0
I think this code will do what you want to achieve. I tested it and it worked for me for the data you have: Code: "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!
