SQLite - Queries with non-English characters don't show any results

Question

I'm using a SearchView widget and it works well, except for the fact the if the string I enter contains any non-English character the result is empty, even if it is clear that there ARE results

Example:

In the image shown above there are two results for the string "chu" but if I type "ñ" the ListView won't show any results and it's obvious that there are results.

This is what I've tried so far

public Cursor searchProductByDescription(String description) {
        String where = PRODUCT_DESCRIPTION + " LIKE LOWER( ? ) ";
        String[] whereArgs = { "%" + description + "%" };   

        this.openReadableDB();
        Cursor cursor = db.query(PRODUCT_TABLE, null, where, whereArgs, null,
                null, null);

        if (cursor != null) {
            cursor.moveToFirst();
        }
        this.closeDB();
        return cursor;
}

I'm not sure if this is an issue related to the searchview widget or to SQLite. I've seen a couple of question in which they suggest converting the string to LOWER o UPPER case, but this solution didn't work for me.

I'd really appreciate if you could help me here. Thanks

Answer 1

LOWER() only knows how to lowercase ASCII characters. LIKE is also case insensitive only with ASCII characters. And ñ is not an ASCII character.

For a solution, consider the following:

• Add another column to your table that you use for searches and use the original column only for display purposes.

• Store data in this column in a normalized form such as NFC and with the case converted consistently to upper/lowercase. For example:

  String stringToStore = Normalizer.normalize(originalString.toLowerCase(), Normalizer.Form.NFC);
  

• Normalize your search strings similarly in code.

If you want to ignore accent e.g. have n also match ñ, use a slightly different approach to remove the accents.