Sqlite android query() / like with accents and without

Question

How to make a query in Sqlite for Android that searches both accented and not accented 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 accent, but also seek São Paulo, and vice versa.

I saw in some questions and on the Internet, that many people recommend to create a normalized column with names without accent and perform the search in both, but I was wondering if there is any other option.

Answer 1

The solution presented even works, but is absurd.

Sqlite works well by default with ASCII and lets the user cope with other options. If you are going to use accent need other text encoding.

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 leotard suitable for this encoding will not be so simple either. There will be some performance gain, but I don’t know if it pays, most of the time it almost pays to use the solution of the question.

Sqlite says it supports both, of course it supports anything, but that doesn’t mean it has the leotard appropriate by default. Has an extension (ICU Extension), or you can use what the 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 there are accents and does not take up extra space, but needs to turn with the extension, never seen a ready.

If you want to do something simpler, there’s a question here that I did and Bacco’s response solves the problem in 99% of the cases, for some people only those cases count.

Having said all this :P, the implementation of Sqlite in Android has the ability to do this, to work should use the option of COLLATE LOCALIZED.