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

Discussion in 'Android Questions' started by asales, Jan 25, 2015.

  1. asales

    asales Well-Known Member Licensed User

    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.
     
  2. imbault

    imbault Well-Known Member Licensed User

    try :
    Code:
    "SELECT City FROM Cities ORDER BY City COLLATE UNICODE"
     
    Livio F and asales like this.
  3. asales

    asales Well-Known Member Licensed User

    This works. Thanks.
     
  4. asales

    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:
    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
     
  5. NJDude

    NJDude Expert Licensed User

    Try:
    Code:
    COLLATE NOCASE UNICODE
     
  6. asales

    asales Well-Known Member Licensed User

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

    Mahares Well Known Member Licensed User

    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
     
    thedesolatesoul and NJDude like this.
  8. asales

    asales Well-Known Member Licensed User

    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.
     

    Attached Files:

  9. KMatle

    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
     
    asales likes this.
  10. Informatix

    Informatix Expert Licensed User

    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.
     
  11. Mahares

    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:
    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
    KMatle likes this.
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice