SQLite android query () / like with accents and without


How to make a query in SQLite for Android that looks for both accented and unstressed words?

For example:

String[]ARGUMENTOS = {"%"+argumento+"%"}

query(TABELA_CIDADES, COLUNA_NOME, NOME + " like ?",ARGUMENTOS,null,null,null,null);

Where the argument for the search would be Sao Paulo, without an accent, but also looking for São Paulo, and vice versa.

I saw in some questions and on the internet that many people recommend creating a standardized column with names without an accent and performing the search in both, but wanted to know if there is any other option.

asked by anonymous 29.01.2016 / 12:43

1 answer


The solution presented even works, but it's absurd.

SQLite works fine by default with ASCII and lets the user turn around with other options. If you are going to use accent needs another encoding of text.

One hypothesis is to use the UTF-8 that people consider universal (there are controversies). But it has a problem, the code needed to handle correct UTF-8 is absurdly larger than SQLite itself.

An alternative is to use UTF-16 (actually UCS-2) that will make the data size large. To implement a suitable collate for this encoding will not be that simple either. There will be some performance gain, but I do not know if it pays off, most of the time it almost makes up for the solution to the question.

SQLite says it supports both. Of course it supports anything. But this does not mean that you have the appropriate collate by default. Has an extension ( ICU extension ). Or you can use what language offers, but rarely is this a viable option.

Another possibility is to use a Latin1 encoding (ISO 8859-1). It is much simpler, as fast as it can be where it has accents and does not take up extra space. But you need to turn around with the extension. I've never seen one ready.

If you want to do something simpler you have a question here that I did and Bacco's answer solves the problem in 99% of cases. For some people only such cases count.

All this said: P, the implementation of SQLite in Android has the ability to do this . To work using the COLLATE LOCALIZED

29.01.2016 / 13:11