20

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!

Elias
  • 583
  • 4
  • 12
  • according to this http://www.sqlite.org/datatype3.html section 6, i don't think sqlite3 supports that sort of things – njzk2 Sep 27 '12 at 14:49
  • 1
    yet, according to this question http://stackoverflow.com/questions/3480999/using-collate-in-android-sqlite-locales-is-ignored-in-like-statement = comparison may work – njzk2 Sep 27 '12 at 14:50
  • Not sure if FTS3/4 can do that somehow but you could try that. http://stackoverflow.com/q/12500799/995891 has issues with those characters though. – zapl Sep 27 '12 at 15:17
  • thanks for your answer njzk2. = comparison works, but not in my case, because the string "cafe" should also find titles like "café au lait". and that's not the case if i use = comparison... but thanks anyway! – Elias Sep 27 '12 at 15:19
  • @zapl: thanks! gonna check that and let you know what i found out! – Elias Sep 27 '12 at 15:28
  • Hey, did you find an answer? If yes please indicate it below. – ılǝ Oct 05 '12 at 01:36
  • If not - are you running the query on a large set of data, is that a frequently run query? A possible but very ineffective workaround would be to parse the search criteria string and to run the query for all possible variations with diacritic marks. For example - query for cafe, café, cafè, câfe, câfé etc. You may have to check for repeated results. – ılǝ Oct 05 '12 at 01:52
  • @ile: i did not really find an answer. i also had the idea of running several queries but that's really a ineffective workaround and not really applicable for my dataset... hope i find something else... – Elias Oct 09 '12 at 08:38
  • @Elias did you manage to get a great solution for this that does not involve creating a column with no diacritics on it? – xarlymg89 Oct 10 '18 at 11:22

1 Answers1

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.

Oliv
  • 8,930
  • 2
  • 43
  • 70
  • thanks for your answer Oliv: "You can create separate table with normalized column" -no, becaus this should be generic. i can't maintain the table ever time something changes... "creating functions is not so easy on sqlite, if even possible" - unfortunately it's not possible... – Elias Oct 09 '12 at 08:40
  • You can update the second table after each fetch... By the way, what do you mean by "the database is fetched from a server"? Do you fetch it yourself? Couldn't the fetch process itself create two columns? – Oliv Oct 10 '12 at 06:28
  • well, the database lies on a server and from time to time the customer uploads a new one. maybe the fetch process could do it, but i then i need an alorithm that tells the process, that é, è and ê is e... will see... – Elias Oct 10 '12 at 09:13