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

Question

When creating my SQLite database in Android I set the database locale - db.setLocale(new Locale("cz_CZ")). This is a Czech locale.

A SELECT statement works and takes the locale into account, for example:

SELECT * from table WHERE name='sctzy' COLLATE LOCALIZED 

Will find the entry 'ščťžý'.

But using LIKE will fail:

SELECT * from table WHERE name LIKE '%sctzy%' COLLATE LOCALIZED 

No row is returned.

BTW. There is no java.text.Normalized class in Android. I thought I could make a second column with a normalized text, stripped of special characters, which would be used for searching - but I am missing a class or way how to normalize the String.

Answer 1

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.