How to Ignore Accent in SQLite Query (Android)

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:

  1. Add another column to your table, for example MOVIE_NAME_ASCII
  2. 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}]", "");
  3. 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, and UNICODE, 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



Leave a reply



Submit