Query in Room SQLite (Insensitive casing) ignoring accents

Question

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?

score 3 · Answer 1 · answered Mar 24 '22 at 01:57

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.

Thank you very much, your solution works, I just want to clarify that if it is necessary to use `WHERE LOWER(FullName)` because I want uppercase or lowercase not to matter when performing the search, for that, `toLowerCase()` is added in `globPersonName()`, another adjustment that I had to make is to add asterisks to the beginning and end of the text entered by the user: `String name = "*" + searchEditText.getText().toString() + "*";` — JP711, Mar 24 '22 at 02:23

score 0 · Accepted Answer · answered Apr 04 '22 at 10:07

Using GLOB was what I needed. However, I changed it a little bit to be a little more "fine-grained". @zen_of_kermit 's solution would also match "Poraz Jaen" for example; but this would not:

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

This way each vowel is replaced by its respective regular expression, so it matches only known variants of each vowel.

score 0 · Answer 3 · answered Sep 12 '22 at 12:55

For those working in Kotlin:

fun replaceVowels(searchText: String): String {
  return searchText.lowercase(Locale.getDefault())
      .replace(Regex("[aáàâã]"), "?")
      .replace(Regex("[eéèëê]"), "?")
      .replace(Regex("[iíìî]"), "?")
      .replace(Regex("[oóòôõ]"), "?")
      .replace(Regex("[uúùüû]"), "?")
}

and change the SQL query to:

@Query("SELECT * FROM your_table WHERE your_column GLOB :query || '*' ")

Query in Room SQLite (Insensitive casing) ignoring accents

Possible solution found on StackOverflow

3 Answers3