Table query in iPhone app

Question

Hello,

I have a tableview (linked to a database) and a search bar. When I type something in the search bar, I do a quick search in the database and display the results as I type.

The query looks like this:

SELECT * FROM MyTable WHERE name LIKE '%NAME%'

Everything works fine as long as I use only ASCII characters. What I want is to type ASCII characters and to match their equivalent with diacritics. For instance, if I type "Alizee" I would expect it to match "Alizée".

Is there a way to do make the query locale-insensitive? I've red about the COLLATE option in SQL, but there seems to be of no use with SQLite.I've also red that iPhone SDK 3.0 has "Localized collation" but I was unable to find any documentation about what this means...

Thank you.

Answer 1

I'm not sure about SQL, but I think you can definitely use the NSDiacriticInsensitivePredicateOption to compare in-memory NSStrings.

An example would be an NSArray full of the strings you're searching over. You could just iterate over the array comparing strings using the NSDiacriticInsensitivePredicateOption as your comparison option and displaying the successful matches.

Answer 2

There are a few options for solving this:

1. Replacing all accented chars in the query before executing it, e.g.

   "Psychédélices" => "Psychedelices"
   "À contre-courant" => "A contre-courant"
   "Tempête" => "Tempete"
   etc.

   but this only works for the input so you must not have accented chars in the database itself. Simple solution but far from perfect.

2. Using a 3rd party library, namely ICU (links below). Not sure if it's the best choice for iPhone though.

3. Writing one or more custom C functions that will do the comparison. More in the links below.

A few posts here on StackOverflow that discuss the various options:
How to sort text in sqlite3 with specified locale?
Case-insensitive UTF-8 string collation for SQLite (C/C++)
How to implement the accent/diacritic insensitive search in Sqlite?

Also a couple of external links:
SQLite and native UNICODE LIKE support in C/C++
sqlite case and accent insensitive searches