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
NET), character set identifiers are treated as case-insensitive, so UTF-8 and utf-8 , as well as Utf-8 or any other variation thereof, always mean the same thing. This would also be the case for other character sets, such as ISO-8859-1 (Latin 1), etc.
By default, it depends on the operating system and its case sensitivity. This means MySQL is case-insensitive in Windows and macOS, while it is case-sensitive in most Linux systems. However, you can change the behavior by changing collation.
Database collation For example, the default server-level collation in SQL Server for the "English (United States)" machine locale is SQL_Latin1_General_CP1_CI_AS , which is a case-insensitive, accent-sensitive collation.
If you want case-insensitive distinct, you need to use UPPER() or LOWER().
For SQLite you have 2 options:
$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();
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With