How to ignore accent in SQLite query (Android)

25
February 24, 2019, at 8:00 PM

I am new in Android and I'm working on a query in SQLite. My problem is that when I use accent in strings e.g.

  • ÁÁÁ
  • ááá
  • ÀÀÀ
  • ààà
  • 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.

Answer 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.

Answer 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.

Answer 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;
READ ALSO
Admob best way to show ads

Admob best way to show ads

Hello experts i want to know what is best way to show admob ads i want know how many ads i put on my application in on app how i can save my account from suspended i many time show my interestial on per user i want everything about ad unit id because...

44
Cursor returns the same columns after “Query, Drop Table, Create Table, Query for the same table name”

Cursor returns the same columns after “Query, Drop Table, Create Table, Query for the same table name”

For example I have a table named myTable in my database file (Android, ps

26
Where are the codeaurora forum blobs

Where are the codeaurora forum blobs

I see commits on GitHub with the message Update SDM blobs from Android Release Tag For example https://githubcom/TheMuppets/proprietary_vendor_xiaomi/commit/380f7b3163013b025a5a20a9b7bb34b32bae96cf but I cannot find the source code of these BLOBs; where can I obtain...

22
RecyclerView onBindViewHolder data leak [on hold]

RecyclerView onBindViewHolder data leak [on hold]

if getscannedentry from firebase value is zero, the textview will not shown "entry" textthe "entry" text only appears for getscannedentry not zero

33