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

android database sqlite locale collate
Daniel Novak picture Daniel Novak · Aug 14, 2010 · Viewed 15k times · Source

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

matto1990 picture matto1990 · Aug 14, 2010

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.

Related questions

How do I view the SQLite database on an Android device?

I have a set of data in an SQLite database. I need to view the database on a device. How do I do that? I have checked in ddms mode. The data in file explorer is empty.

Read More
What are the best practices for SQLite on Android?

What would be considered the best practices when executing queries on an SQLite database within an Android app? Is it safe to run inserts, deletes and select queries from an AsyncTask's doInBackground? Or should I use the UI Thread? I …

Read More
Best way to work with dates in Android SQLite

I'm having some trouble working with dates on my Android application that uses SQLite. I have a couple questions: What type should I use to store dates in SQLite (text, integer, ...)? Given the best way to store dates how do …

Read More