• Collation for unicode/accent insensitive queries

    By Tommy Valand 1 decade ago

    I have an application where users can search for last name, first name, address, etc.

    I've managed to get unicode case insensitive searches working by converting the lookup value to upper case and adding UPPER around the field name in the query.

    E.g. SELECT * FROM people WHERE UPPER(firstName)='ØYVIND'

    I've yet to discover how to do accent insensitive queries. If I want all people named Per, regardless of accent (e.g. Pér).

    I couldn't find a way to create a custom collation at runtime. If possible, could you add a UTF-8 case insensitive/accent insensitive collation?

    • I don't really know how this would get implemented

      By Philippe Riand 1 decade ago

      It might be a complex problem, as the index has to work similarly (e.g. searching and finding the data with the appropriate collation). The best solution would be to deduce the SQLite collation from the view column itself. 

      Are you comfortable with the source code and, if so, can you get a look at it? I don't plan to evaluate it myself in the short term.

      • I probably wouldn't be able to implement this myself in a reasonable time frame

        By Tommy Valand 1 decade ago

        I have no experience with creating JARs or writing C (not sure which is required for implementing this myself). I totally understand if you don't have the time to implement this.

        Not sure if it's faster than implementing a custom collation, but I found a code snippet on StackOverflow for a user defined function that strips accents from strings. Is this something that you're familiar with/would consider implementing?

        http://stackoverflow.com/questions/10356062/how-to-deal-with-accented-characters-in-ios-sqlite

        Even if that's not possible, thanks for all the great work you and your team are doing for the community! :)