PHP SQLite case-insensitive LIKE for Unicode strings

05.04.2011

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.

Метки: , , , , , ,
Написать комментарий | Короткая ссылка

Комментариев: 8

  1. YuriKolovsky:
    08.04.2011 в 18:53

    The only solution that worked from the hundreds that I tested that does not involve compiling SQLite with ICU, which is often impossible due to server restrictions.

    you ROCK!

  2. Max:
    23.05.2011 в 05:11

    Great solution!

  3. Peter:
    03.10.2011 в 02:56

    Brilliant! Thanks!

    Can you do a similar trick for ORDER? I need to sort entries containing Umlauts (öäü!AÖÜ) and Accents (éàè).

  4. Lexa:
    03.10.2011 в 09:50

    Peter,

    Glad that you have found the solution useful.

    For accent-insensitive ORDER BY try this:
    http://pastebin.com/vpbff6fJ

  5. remort:
    14.12.2011 в 13:07

    what about sqlite3 without pdo. is it possible to create similar function with «sqlite_create_function» in php?
    i’ll try to make something like this now, but i’ve never dig so deep in sql and php before, so if you can — give a simple solution for sqlite3 in php please.

  6. remort:
    14.12.2011 в 15:17

    yeah, it works:

    function ci_search($mask, $value){
    // same code
}

// $db is SQLite3 object
$db->createFunction('like', 'ci_search', 2);
$query_firmname = $db->query('SELECT * FROM company WHERE name like "%'.$companyname.'%"');
  7. Carlos:
    10.06.2012 в 16:57

    Hi,

    A good explanation and a great example.
    Thank you very much for sharing it!

  8. mvel:
    04.08.2012 в 02:30

    Nice hint, thank you.
    What a dumb db engine, btw… But sometimes we have no options.

Ваш комментарий

◂
▸