How to ignore accent in SQLite query (Android)
Generally, string comparisons in SQL are controlled by column or expression COLLATE
rules. In Android, only three collation sequences are pre-defined: BINARY (default), LOCALIZED and UNICODE. None of them is ideal for your use case, and the C API for installing new collation functions is unfortunately not exposed in the Java API.
To work around this:
- Add another column to your table, for example
MOVIE_NAME_ASCII
Store values into this column with the accent marks removed. You can remove accents by normalizing your strings to Unicode Normal Form D (NFD) and removing non-ASCII code points since NFD represents accented characters roughly as plain ASCII + combining accent markers:
String asciiName = Normalizer.normalize(unicodeName, Normalizer.Form.NFD)
.replaceAll("[^\\p{ASCII}]", "");Do your text searches on this ASCII-normalized column but display data from the original unicode column.
Ignore accents SQLite3
There has been a similar question here.
They said it is not really possible on Android, but there is a workaround with an additional normalized column.
Query in Room SQLite (Insensitive casing) ignoring accents
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.
Android - SimpleAdapter filter query, how to ignore accents?
I would see 2 options here :
- Create a table that contains accent-less version of the contacts names and a reference to the actual contact Id
- Replace accented caracters by ? in your search (which may result in not really user expected behaviour, but is so much simpler)
SQLite accent-insensitive search
Set up a collation using sqlite3_create_collation
and then use it like this:
SELECT * FROM users WHERE name LIKE "Andre%" COLLATE NOACCENTS
Ignore accents in sqlite query appcelerator
UNICODE
isn't a built in collation sequence. The ICU extension lets you define locale-specific Unicode aware collators, but it isn't built by default in a lot of sqlite3 installs.
Problems ordering sqlite by a column with accented characters (Á)
Either
... ORDER BY column COLLATE UNICODE
or
... ORDER BY column COLLATE LOCALIZED
Reference:
In addition to SQLite's default
BINARY
collator, Android supplies two more,LOCALIZED
, which changes with the system's current locale, andUNICODE
, which is the Unicode Collation Algorithm and not tailored to the current locale.
Example:
db.execSQL("CREATE TABLE foo(a TEXT);");
db.execSQL("INSERT INTO foo VALUES('Antonio'),('Bonzo'),('Zeto'),('Ángela');");
Cursor c = db.rawQuery("SELECT * FROM foo ORDER BY a COLLATE UNICODE", null);
while (c.moveToNext()) {
Log.d("foo", c.getString(0));
}
Output:
Ángela
Antonio
Bonzo
Zeto
Fetch contacts ignoring accents
Use the CONTENT_FILTER_URI
API to quickly search contacts by name, this should handle accents automatically for you:
String name = "jero";
Uri searchUri = Uri.withAppendedPath(Contacts.CONTENT_FILTER_URI, Uri.encode(name));
Cursor cur = getContentResolver().query(searchUri, null, null, null, null);
DatabaseUtils.dumpCursor(cur);
if (cur != null) cur.close();
Related Topics
Bitmapfactory Oom Driving Me Nuts
Finish an Activity from Another Activity
Unable Instantiate Android.Gms.Maps.Mapfragment
How to Disable Action Bar Permanently
How to Get the Touch Position in Android
Android: Out of Memory Exception in Gallery
Dynamically Changing the Fragments Inside a Fragment Tab Host
Pass a String from One Activity to Another Activity in Android
How to Kill an Application with All Its Activities
Passing Arraylist of Objects Between Activities
Get the Current Language in Device
Datepicker: How to Popup Datepicker When Click on Edittext
How to Prevent Multiple Instances of an Activity When It Is Launched with Different Intents
Callback to a Fragment from a Dialogfragment
How to Set Opacity (Alpha) for View in Android