How to ignore accent in SQLite query (Android)

问题:

I'm new in adnroid and I'm working on a query in sqlite, but the problem is when I use accent in my string. Ex:

• ÁÁÁ
• ááá
• ÀÀÀ
• ààà
• aaa
• AAA

If I do:

SELECT * FROM TB_MOVIE WHERE MOVIE_NAME LIKE '%a%' ORDER BY MOVIE_NAME;

It's return:

• AAA
• aaa (It's ignoring the others)

But if I do:

SELECT * FROM TB_MOVIE WHERE MOVIE_NAME LIKE '%à%' ORDER BY MOVIE_NAME;

It's return:

• ààà (ignoring the title "ÀÀÀ")

I want to select strings in a SQLite DB without caring for the accents and the case. Please help.

回答1:

Generally, string comparisons in SQL are controlled by column or expression COLLATE rules. In Android, only three collation sequences are pre-defined: BINARY (default), LOCALIZED and UNICODE. None of them is ideal for your use case, and the C API for installing new collation functions is unfortunately not exposed in the Java API.

To work around this:

1. Add another column to your table, for example MOVIE_NAME_ASCII

2. Store values into this column with the accent marks removed. You can remove accents by normalizing your strings to Unicode Normal Form D (NFD) and removing non-ASCII code points since NFD represents accented characters roughly as plain ASCII + combining accent markers:

   String asciiName = Normalizer.normalize(unicodeName, Normalizer.Form.NFD)
       .replaceAll("[^\\p{ASCII}]", "");
   

3. Do your text searches on this ASCII-normalized column but display data from the original unicode column.

回答2:

You can use Android NDK to recompile the SQLite source including the desired ICU (International Components for Unicode). Explained in russian here: http://habrahabr.ru/post/122408/

The process of compiling the SQLilte with source with ICU explained here:

How to compile sqlite with ICU?

Unfortunately you will end up with different APKs for different CPUs.

回答3:

You need to look at these, not as accented characters, but as entirely different characters. You might as well be looking for a, b, or c. That being said, I would try using a regex for it. It would look something like:

SELECT * from TB_MOVIE WHERE MOVIE_NAME REGEXP '.*[aAàÀ].*' ORDER BY MOVIE_NAME;