[FIXED] Query in Room SQLite (Insensitive casing) ignoring accents

Issue

In a part of my Android application you can search for people by name. But I need that even if the name or surname is written incorrectly (without an accent for example) the result is displayed:

Example:

In database: Pérez Juan, Cooper Sheldon, Pérez Adrian

Search: perez

Show: Pérez Juan, Pérez Adrian (from database)

I was performing the query using LIKE and it works covering the insensitive casing, but it doesn't work for accents because I have to write the letter exactly the same (with or without an accent), here is my working code:

So I get the variable used in searchPerson

String name = searchEditText.getText().toString();
            if (!name.equals("")){
                name = "%" + name + "%";
            }

PersonDao

@Query("SELECT *, lastName || ' ' || name AS FullName\n" +
        "FROM Person\n" +
        "WHERE FullName LIKE :fn\n" +
        "ORDER BY FullName")
LiveData<List<Person>> searchPerson(String fn);

---

Possible solution found on StackOverflow

---

To solve the problem of the accents I found this answer that I am trying to implement without success

So I get the variable used in searchPerson

String name = searchEditText.getText().toString();
            name = addTildeOptions(name);

addTildeOptions function

private String addTildeOptions(String searchText) {
    return searchText.toLowerCase()
            .replaceAll("\\[aáàäâã]\\.*", "[aáàäâã]")
            .replaceAll("\\[eéèëê]\\.*", "[eéèëê]")
            .replaceAll("\\[iíìî]\\.*", "[iíìî]")
            .replaceAll("\\[oóòöôõ]\\.*", "[oóòöôõ]")
            .replaceAll("\\[uúùüû]\\.*", "[uúùüû]")
            .replace("*", "[*]")
            .replace("?", "[?]");
}

PersonDao

@Query("SELECT *, lastName || ' ' || name AS FullName\n" +
        "FROM Person\n" +
        "WHERE lower(FullName) GLOB :fn\n" +
        "ORDER BY FullName")
LiveData<List<Person>> searchPerson(String fn);

However this does not return any results, even if I type a single letter it never shows anything.

I've tried adding asterisks to the start and end when getting the text of the EditText, tried to enter the addTildeOptions function in my PersonDao query as the answer shows but failed to do so, tried some other non-relevant things but never got results.

What is wrong in my code?

Solution

The reason your newer query fails is that you are passing a lower-cased string to the WHERE clause, and no rows match those lowercase strings. For example, your table contains "Pérez Juan", not "pérez juan"

The addTildeOptions function is on the right track, but you need to change it to replace all plain vowels and those with diacritical marks (tilde, accent, umlaut, etc.) with a '?' character so that the query can find rows using either LIKE or GLOB:

String globPersonName(String fn) {
    return fn.replaceAll("[aáàäâã]", "?")
            .replaceAll("[eéèëê]", "?")
            .replaceAll("[iíìî]", "?")
            .replaceAll("[oóòöôõ]", "?")
            .replaceAll("[uúùüû]", "?");
}

Then just remove the lower call and keep the GLOB in your query:

@Query("SELECT *, lastName || ' ' || name AS FullName\n" +
        "FROM Person\n" +
        "WHERE FullName GLOB :fn\n" +
        "ORDER BY FullName")
LiveData<List<Person>> searchPerson(String fn);

So a search for all variations of a name would look like: personDao.searchPerson(globPersonName(fn));

For example, globPersonName("Perez Juan") will return P?r?z J??n and your query should find the "Pérez Juan" record.

Answered By - zen_of_kermit
Answer Checked By - Robin (JavaFixing Admin)