What is the reason for this query give no results:
SELECT name FROM users WHERE LOWER(name) LIKE LOWER('%после%');
When this works fine:
SELECT name FROM users WHERE LOWER(name) LIKE LOWER('%После%');
Name is 'Последователь'. If i use name like 'Post', then search works fine.
Version: PostgreSQL 11.2 (Ubuntu 11.2-100) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.3.0-27ubuntu1~18.04) 7.3.0, 64-bit
Server and db encoding is UTF8. Client encoding is UNICODE.
The lower function works according to the database collation, so the result will vary depending on how your database is defined.
It will work well in this case:
test=# CREATE DATABASE rus TEMPLATE template0
ENCODING UTF8 LC_COLLATE "ru_RU.utf8" LC_CTYPE "ru_RU.utf8";
CREATE DATABASE
test=# \c rus
You are now connected to database "rus" as user "postgres".
rus=# SELECT LOWER('%после%') = LOWER('%После%');
?column?
----------
t
(1 row)
But it won't work with the C collation, because that does not know how to properly lower case cyrillic characters:
rus=# \c test
You are now connected to database "test" as user "postgres".
test=# DROP DATABASE rus;
DROP DATABASE
test=# CREATE DATABASE rus TEMPLATE template0
ENCODING UTF8 LC_COLLATE "C" LC_CTYPE "C";
CREATE DATABASE
test=# \c rus
You are now connected to database "rus" as user "postgres".
rus=# SELECT LOWER('%после%') = LOWER('%После%');
?column?
----------
f
(1 row)
So if you want your query to work well, make sure that you are using a collation that knows how to convert the characters to upper and lower case.
Look Closer. LIKE statement is sometimes case sensitive. Depens on the program you use
The first statement is :
SELECT name FROM users WHERE LOWER(name) LIKE LOWER('%после%');
while the second is :
SELECT name FROM users WHERE LOWER(name) LIKE LOWER('%После%');
What you seek is
П оследователь
note that in the first statement you wrote with (п) while the second one is (П)