SQLite refuses to work with lower() on Windows-1251

Question

PHP

Habr, help, the deadline is coming, but the delivery of the project is not.
There was a problem - lower() does not work in sql queries to the SQLite database on the hosting.
Request:

... WHERE lower(Name) LIKE lower('$startWith%')

Name - the name of the country. For example, "Russia".
If $startWith has the same value as the entry in the database, everything is ok. Trouble occurs when the values ​​are not equal in case, for example, "Russia".
What should I do?
Everything works well at home.
Home: windows 7, denwer
Hosted: Apache 2.* [l24, Linux], aux3 / mod_php; SQLite Library 3.7.7.1; SQLite Encoding iso8859

I really hope for your help.

PS Please don't ask why I need windows-1251.

Answer 1

Add a collation function to sqlite and use it.

Answer 2

Well, select lower(Name)… what does it produce?

Answer 3

Not very familiar with SQLite. Try to convert strings to uppercase when comparing strings, i.e. UPPER.

Answer 4

From the official docs:
>lower(X) The lower(X) function returns a copy of string X with all ASCII characters converted to lower case. The default built-in lower() function works for ASCII characters only. To do case conversions on non-ASCII characters, load the ICU extension.
The windows-1251 encoding is not quite normal ASCII, probably from here all the troubles. Read more here www.sqlite.org/fts3.html A
lot of people face a similar problem when searching for strings in UTF in SQLite, you can google for this case.

Answer 5

Try case insensitive ILIKE if available in SQLite.

Answer 6

For php:

$db = new SQLite3('1406-2280.sqlite');
function php_nocase($string) {
    return strtolower($string);
}
$db->createFunction('php_nocase', 'php_nocase');
$sql = '
SELECT * 
FROM NAME_AND_RUB  as nr
WHERE php_nocase(nr.name) LIKE php_nocase("%горь%")
';