Using Collate in Android Sqlite - Locales Is Ignored in Like Statement

Using COLLATE in Android SQLite - Locales is ignored in LIKE statement

Have you had a look at the SQLite documentation for LIKE? It has come information about non ASCII characters and a bug. Maybe Android has an older version of SQLite installed where this is a problem.

I think the second normalised column might be your best option unfortunately.

How to use SQL COLLATE operator with Android CursorLoader

I have found the answer, but it is not what I hoped for. Convenient COLLATE LOCALIZED was removed from Android around Nougat release and is not replaced in any way.

Known workarounds are:

• Perform filtering in coding (use Normalizer.normalize or Collator java object)
• Add additional column to the filtered table with normalized name (no accents etc.) and compare your users input to this column
• Add custom COLLATOR to SQLite database, what sounds cool but there is not Java API to do so. You would have to use Anroid NDK and write it in C++ code.

I found these in hire if you wish to get detailed answears:
https://discuss.zetetic.net/t/workaround-for-using-androids-localized-collator-after-3-5-0-update/1515/14

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.

Accented Search in sqlite (android)

COLLATE NOCASE works only for the 26 upper case characters of ASCII.

Set the database's locale to one that has accented character support using setLocale() and use COLLATE LOCALIZED.

You may also try using COLLATE UNICODE.
But beware of this bug: SQLite UNICODE sort broken in ICS - no longer case-insensitive.

Check the documentation for mention of these two collators in Android.

Also check out this online collation demo tool.

Special chars like Umlaute are not saved correctly in android sqlite database

As I know values.put accepts those characters like "ä". For some reason It can be saved in SQLite after converting that into Unicode character like "\u00E4". Simply in such case Save it in SQLite as it is and convert "\u00E4" again into "ä" when retrieving that data. Here is the method that returns those characters converting from Unicode characters.

public String getCharacterFromUnicode (String unicodeChar){    
    String returnString = null
    try {
        byte[] utf8 = unicodeChar.getBytes("UTF-8");
       returnString = new String(utf8, "UTF-8");
    }catch (Exception ex){    
    }
    return returnString;
}

Which returns your "\u00E4" to "ä".

UPDATE :

If you are not sure which parts is converted to Unicode then use Apache commons-lang library to escape those,

myString = org.apache.commons.lang.StringEscapeUtils.unescapeJava(myString);

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.

Case sensitive and insensitive like in SQLite

You can use the UPPER keyword on your case insensitive field then upper-case your like statement. e.g.

SELECT * FROM mytable 
WHERE caseSensitiveField like 'test%' 
AND UPPER(caseInsensitiveField) like 'G2%'

Android l10n / i18n database. Is the table android_metadata intended for this?

Android updates that field each time you open the database to the current default locale. That should be the system language unless you change the default in your app.

SQLiteDatabase#openDatabase

db.setLocale(Locale.getDefault());

It should be save to use that field but I have never tested it.

The purpose of that table is AFAIK to allow you to use COLLATE LOCALIZED in your statements - or not if you specify NO_LOCALIZED_COLLATORS

If you want to have a database of texts in different languages then consider using your own table of languages maybe as foreign key to you text table.

How do I make a case insensitive query with ContentResolver in Android?

Please try (I suppose that mail is String object):

Cursor cursor = context.getContentResolver().query(ContactsContract.CommonDataKinds.Email.CONTENT_URI,
        new String[] {Contacts._ID}, "lower("+ContactsContract.CommonDataKinds.Email.ADDRESS + ")=lower('"+ email +"')",
        null, null);

or

Cursor cursor = context.getContentResolver().query(ContactsContract.CommonDataKinds.Email.CONTENT_URI,
        new String[] {Contacts._ID}, "lower("+ContactsContract.CommonDataKinds.Email.ADDRESS + ")=lower('"+ email +"')",
        new String[] {}, null);

---

Related Topics