Sqlite игнорировать регистр в условии WHERE

#android #кириллица #sqlite #sql

Подскажите как на андроиде в sqlite базе сделать выборку на кирилице, игнорируя регистр?
запросы с LIKE, COLLATE NOCASE и UPPER(column_name) работают только для английских
букв! А для русских нет!
Для примера, имею таблицу favourite  с записью title = Тест, юзер пытается запросить
её но в поле поиска вводит слово тест маленькими буквами.
пробовал такие варианты:

SELECT * FROM favourite WHERE title LIKE '%тест%'  
SELECT * FROM favourite WHERE UPPER(title) LIKE UPPER('%тест%')  
SELECT * FROM favourite WHERE title LIKE '%ТЕСТ%' COLLATE NOCASE

все три варианта не подходят,  больше ничего не нагуглил. Подскажите пожалуйста решение 
ps: запросы осуществляю так:

SQLiteDatabase db = getWritableDatabase(); //*extends SQLiteOpenHelper*  
Cursor cursor = db.rawQuery(sql, null);
    

Ответы

Ответ 1

Вообще-то это известный баг или фича (кому как) SQLite. Он не понимает case-insensitive
where/like/glob, когда символы не в ASCII. Как говорит мануал:

A bug: SQLite only understands upper/lower case for ASCII characters by default.
The LIKE operator is case sensitive by default for unicode characters that are beyond
the ASCII range. For example, the expression 'a' LIKE 'A' is TRUE but 'æ' LIKE 'Æ'
is FALSE.)

Единственный способ обойти это ограничение, это завести хранимую процедуру выполняющую
операцию сравнения.
Update Я слегка погорячился по поводу хранимых процедур в SQLite. SQLite не поддерживает
хранимые процедуры в общепринятом смысле, нужно писать т.н. custom function на С, пример
здесь


Ответ 2

В android такое невозможно. sqlite там собран без ICU, возможность загрузки экстеншнов
отключена. Переопределить там ничего не получится, case_sensitive_like тоже бесполезен.
Есть несколько обходных вариантов.


Добавить в базу ещё одно поле title_lc, в котором хранить то же значение, что и в
title, но в нижнем регистре. Преобразование производить на java при помощи String#toLowerCase(Locale.getDefault())


В android sqlite есть 2 нестандартных collator'а: LOCALIZED и UNICODE. Collator указывается
в определении столбца при создании БД: 
CREATE TABLE t1(title TEXT COLLATE LOCALIZED);


Сравнение строк будет регистронезависимым (SELECT * where title = "ТеСт";) .
  Минусы: 
     * COLLATE UNICODE часто не работает; 
     * при использовании COLLATE LOCALIZED нужно указывать локаль для БД: (SQLiteDatabase#setLocale(new
Locale("ru_RU")));
     * и самый большой минус в том, что LIKE не работает.

Воспользоваться не родной сборкой sqlite. Например, sqlcipher можно пересобрать вручную,
включив ICU.


Ответ 3

Для SQLite обычно требуется выставить переменную case_sensitive_like

PRAGMA case_sensitive_like=OFF;


Смотрите результат

Можно через костыль

SELECT * FROM favourite WHERE UPPER(title) LIKE '%ТЕСТ%' 


Предварительно тест поднять в верхний регистр на уровне приложения toUpperCase().

Резюме:

Для UTF-8 необходимо переопределять LIKE. Есть вариант, как это сделать на PHP (через
RegEx-ы). Для Android SDK смотрите в сторону SQLiteDatabase.CustomFunction()