Android, Diacritic Insensitive SQLite Search

Question

i'm trying to query a SQLite database in android where french characters with accents should be handled like normal latin characters, for example:

SELECT * FROM x WHERE y LIKE %cafe%

should return

café, câfè, ...

I have googled for a whole day now and read all concerning posts on stackoverflow. The possibility to add a column to the table that includes normalized titles is no option, because the database is fetched from a server and maintained by a third party.

Using collations mentioned in other posts like

Latin1_general_CI_AI

is also no option, because SQLite only supports 3 (in android 5) collations that don't help me.

Setting the database in android to

Locale.FRENCH

and using collation

COLLATE LOCALIZED

also doesn't do the trick.

I know there is some flag in iOS (DiacriticInsensitiveSearch) that does it automatically and so my hope is that something like this is available for android too.

Any ideas? Thanks in advance!

Answer 1

The possibility to add a column to the table that includes normalized titles is no option, because the database is fetched from a server and maintained by a third party.

You can create separate table with normalized column and a foreign key to the original table. I think this is the only option you have with sqlite on android.

Or if you could somehow create user-defined function remove_diacritics, then you would select like this:

SELECT * FROM x WHERE remove_diacritics(lower(y)) 
LIKE remove_diacritics(lower(%cafe%))

But beware an index on x.y won't get used. You also might not need lower. But as far as I know creating functions is not so easy on sqlite, if even possible.