case sensitive LIKE on VARCHAR field with cp1251 encoding

Question

I have an InnoDB table with VARCHAR(250) cp1251_general_ci field named comment.

I'm trying to search on this field, case sentative.

SELECT comment
FROM body_legend
WHERE comment LIKE '%ТТ%'
GROUP BY comment

works as expected, but its case insensitive.

I tried to use BINARY like

SELECT comment
FROM body_legend 
WHERE comment LIKE BINARY '%ТТ%'
GROUP BY comment`

it returns an empty result.

I tried to use COLLATE like

SELECT comment
FROM body_legend
WHERE comment LIKE '%ТТ%' COLLATE cp1251_general_ci

it returns error

 COLLATION 'cp1251_general_ci' is not valid for CHARACTER SET 'utf8mb4'

How to make search case sensitive? I would be glad if the answer is complemented by a description of why did not work my queries.

@Mihai SELECT comment FROM body_legend WHERE BINARY comment LIKE '%ТТ%' return an empty result — Sergey Novikov, Sep 12 '14 at 11:50
How about `WHERE comment COLLATE latin1_general_cs LIKE '%ТТ%'` Assuming you have TT somewhere in there — Mihai, Sep 12 '14 at 11:53
@Mihai #1253 - COLLATION 'latin1_general_cs' is not valid for CHARACTER SET 'cp1251' — Sergey Novikov, Sep 12 '14 at 11:55
http://stackoverflow.com/questions/16561447/why-cant-i-use-a-specific-collation-in-mysql http://stackoverflow.com/questions/5629111/how-can-i-make-sql-case-sensitive-string-comparison-on-mysql — Mihai, Sep 12 '14 at 11:56
Read both topics but its not help me. Tried all combinations of COLLATE. Is it possible to conver '%TT%' to cp1251_general_ci? — Sergey Novikov, Sep 12 '14 at 13:08

score 0 · Answer 1 · answered Sep 12 '14 at 13:17

Self-answer:

SELECT comment
FROM body_legend
WHERE comment
LIKE BINARY CONVERT( '%ТТ%' USING cp1251);

It seems that if I write this query in the phpMyAdmin it is read as the utf8 and in bit-wise comparison with the value in the cp1251 table, of course, is not the same.

case sensitive LIKE on VARCHAR field with cp1251 encoding

1 Answers1