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
source to share
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 .
source to share