3

Actually question was asked several times, but I didn't manage to find answer.

There's set of SQLite table(s) which are read-only - I can't change their structure or redefine collation rules. Tables consisting some international characters (Russian/Chinese, etc).

I would like to get some case-insensitive selection like:

select name from names_table where upper(name) glob "*"+constraint.toUpperCase()+"*"

It works only when name is latin/ASCII charset, for international chars it doesn't work.

SQLite's manual reads:

The upper(X) function returns a copy of input string X in which all lower-case ASCII characters are converted to their upper-case equivalent.

So the question is: how to resolve this issue and make international chars in upper/lower case?

Barmaley
  • 16,638
  • 18
  • 73
  • 146

2 Answers2

6

This is known problem in sqlite. You can redefine built-in functions via Android NDK. This is not a simple way. Look at this question

Notice that indexes of your tables will not work (for UDF) and query can be very slow.

Instead of it you can store your data (which you look for) in other column in ascii format.

For example:

"insert into names_table (name, name_ind) values ('"+name+"',"+"'"+toAsciiEquivalent(name)+"')"

name    name_ind
----------------
Имя     imya
Name    name
ыыы     yyy

and search string by column name_ind

select name from names_table where name_ind glob "*"+toAsciiEquivalent(constraint)+"*"

This solution requires more space for data, but it is simple and fast.

Community
  • 1
  • 1
matreshkin
  • 2,199
  • 18
  • 28
  • You could use another database with needed tables and columns and attach this DB to an original one. In this case the process of synchronization will be more complicated, however. – matreshkin Mar 03 '15 at 09:13
  • @matreshkin Hi! Can you please be more specific on toAsciiEquivalent() method implementation? Maybe some code example? Also am I right that it can be used with LIKE statement as well? – AlexKost Aug 13 '15 at 13:20
  • The implementation of toAsciiEquivalent() strictly depends on the language you use. Actually, this function should transliterate words to the latin alphabet. An example for Russian can be found on this page (see toTranslit function): http://www.sql.ru/forum/502128/russkiy-translit – matreshkin Aug 13 '15 at 14:50
  • And Yes, Like statement will work for those translitirated strings as intented - indexing will work, search will be fast – matreshkin Aug 13 '15 at 14:53
1

Instead of providing full Unicode case support by default, SQLite provides the ability to link against external Unicode comparison and conversion routines. The application can overload the built-in NOCASE collating sequence (using sqlite3_create_collation()) and the built-in like(), upper(), and lower() functions (using sqlite3_create_function()). The SQLite source code includes an "ICU" extension that does these overloads. Or, developers can write their own overloads based on their own Unicode-aware comparison routines already contained within their project.

Reference: http://www.sqlite.org/faq.html

Vijendra Singh
  • 628
  • 3
  • 13