blog.amartynov.ru

Official SQLite docs state:

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.

It’s the major inconvenience in using SQLite. Googling for a solution gives links to a lot of discussions, especially about enabling a certain ICU extension.

I am here to give you a complete answer to the question How to make the LIKE operator case-insensitive for UTF-8 strings when using SQLite via PHP PDO:

$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();

The main idea is to override the default implementation of the LIKE function by using the PDO::sqliteCreateFunction call. It’s what the official FAQ suggests.

Advantages:

• it works
• no need to recompile anything

Disadvantages:

• it’s obviously slower than the native implementation
• The PDO::sqliteCreateFunction method is currently marked experimental

Anyway, I hope this post will help you.