phpmyadmin Question posted in
The official documentation can be found here.

MySQL "LIKE" ignores result with Russian words – Phpmyadmin

I have a table with titles in Cyrillic alphabet. LIKE seems to ignore certain words in Russian. Collation UTF8_general_ci. I tried execute request from php as well as from PHPMyAdmin. What can be possible reason?

SELECT * FROM `table` WHERE `column` LIKE 'слово'

UPD: Here is example: http://sqlfiddle.com/#!9/30d904/1/0

Tags:

3

Answers


  1. 0

    try like this :

    SELECT * FROM videos WHERE title LIKE '% все %'

    just add % at the beginning and the end of your search string (word).

    for example. change 'все' to '% все %'.

    Your Example

    But if you want it to be not case sensitive then

    SELECT * FROM videos WHERE LOWER(title) LIKE LOWER('% все %')

    Not case sensitive Example

    Login or Signup to reply.
  2. 0

    When you use 

    WHERE title LIKE 'все'

    the query matches titles that are equal to 'все' because LIKE without wildcards is equivalent to = (with the possible exception of case sensitivity).
    But you want titles that contain the word 'все', meaning titles that would be anywhere inside the column value preceded and followed by a space (except when it is at the start or at the end).
    So use the operator LIKE like this:

    SELECT * FROM videos 
WHERE CONCAT(' ', title, ' ') LIKE '% все %'

    See the demo.
    Results:

    | ID  | title                      | description | upload_time         | file_name                          | hosted | video_uri | play_count |
| --- | -------------------------- | ----------- | ------------------- | ---------------------------------- | ------ | --------- | ---------- |
| 539 | И все будет хорошо дай бог |             | 2019-12-17 05:01:25 | uploads/4766__все_будет_хорошо.mp4 | 1      | 379928030 | 1          |
| 566 | Да и вообще вы все молодцы |             | 2019-12-17 22:47:47 | uploads/7118__все_молодцы.mp4      | 0      |           | 0          |
| 614 | Ну это сразу все объясняет |             | 2019-12-20 09:19:04 | uploads/3522__это_сразу_все_.mp4   | 1      | 380654168 | 2          |
    Login or Signup to reply.
  3. 0 
    SELECT * 
FROM videos 
WHERE LOWER(`title`) REGEXP '[[:<:]]все[[:>:]]'

    http://sqlfiddle.com/#!9/62292ea/1

    The regular expression pattern works with word boundaries (space, comma, etc.) as well as with begin|end of string. I applied lower() function for case insensitivity.
    So it matches “Все в гости к нам” and doesn’t match “Повсеместно”

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search