Trouble finding the letter "Y" in PostgreSQL 8.4

Question

PostgreSQL

Good morning, dear habrazhiteli. Faced now with the strange behavior of PostgreSQL, when searching by the name and surname of users.

By chance, I have to use PostgreSQL 8.4, and ran into a problem.

There is a line in the request:

AND (ARRAY_TO_STRING(ARRAY[first_name, last_name] , ' ') ILIKE :search

There are, for example, three people in the database, “Vasily Ivanov”, “Ingeborga Dapkunaite” and “Mikhail Mikhailov”.
I'm trying to pull out from the table all the records for which I substitute just one letter "Y" in the parameter specified in the query above.

Result: Ingeborg and Mikhail are found, Vasily is swept aside from the result.

It turns out that the problem occurs if the letter "Y" is at the end of the first of the two connected words, in this case, "Vasily".

The question is why is this happening and how can it be avoided?

Answer 1

1) What encoding and locale do you use?
2) On requests like

select '1' where 'Василий Иванов' ilike '%Й%'

does the error reproduce?
3) so, probably, it is better to glue the lines:

AND first_name || ' ' || last_name ILIKE :search

Answer 2

A similar situation. Let's say there are two strings in the database with the same words in UTF-8 encoding, locale RU:
1. Khanty-Mansiysk Autonomous Okrug
2. Khanty-Mansiysk Autonomous Okrug

select * from test where like (% Mansi%);

we get the result equal to one line with number 1. At the same time, if I enter a query

select * from test where like (% Mansi%);

then I get the result equal to one line with number 2. At the same time, please note that the requests have different endings "Y" and "I".