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!
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With