Case insensitive utf8 select

Question

In SQLite I want to case-insensitive "SELECT LIKE name" works fine for normal latin names, but when the name is in UTF-8 with non-latin characters then the select becomes case-sensitive, how to make it also case-insensitive like latin characters?

p.s. my sqlite is v3 and I connect with PHP PDO

Answer 1

For SQLite you have 2 options:

1. compile it with ICU support: How to compile, Compilation options
2. override the LIKE function, here is a complete solution (from http://blog.amartynov.ru/?p=675)

$pdo = new PDO("sqlite::memory:");

# BEGIN

function lexa_ci_utf8_like($mask, $value) {
    $mask = str_replace(
        array("%", "_"),
        array(".*?", "."),
        preg_quote($mask, "/")
    );
    $mask = "/^$mask$/ui";
    return preg_match($mask, $value);
}

$pdo->sqliteCreateFunction('like', "lexa_ci_utf8_like", 2);

# END

$pdo->exec("create table t1 (x)");
$pdo->exec("insert into t1 (x) values ('[Привет España Dvořák]')");

header("Content-Type: text/plain; charset=utf8");
$q = $pdo->query("select x from t1 where x like '[_РИ%Ñ%ŘÁ_]'");
print $q->fetchColumn();