SQLite - Queries with non-english characters show no results
I am using the SearchView widget and it works well except for the fact that if the line I was entering contains any non-english character the result is empty even though it is clear that there are ARE results
Example:
In the above image, there are two results for the string "chu" , but if I type "-" the ListView does not show any results, and it will be obvious that there are results.
This is what I have 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 a searchview or SQLite widget related issue. I've seen a couple of questions where they suggest converting a string to a LOWER o UPPER case, but this solution didn't work for me.
I would really appreciate if you could help me here. Thanks to
LOWER()
knows only as lowercase ASCII characters. LIKE
also case insensitive with ASCII characters only. And is ñ
not an ASCII character.
To solve, consider the following:
-
Add another column to the table that you are using for search and use the original column for display only.
-
Store the data in this column in normalized form such as NFC and in case the case is converted sequentially to upper / lower case. For example:
String stringToStore = Normalizer.normalize(originalString.toLowerCase(), Normalizer.Form.NFC);
-
Normalize search strings similarly in code.
If you want to ignore the accent, eg. have n
also match ñ
, use a slightly different approach to remove accents .