MySQL "LIKE" ignores result with Russian words
mysql free download
how to install mysql
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
SELECT * FROM videos WHERE LOWER(`title`) REGEXP '[[:<:]]все[[:>:]]'
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 "Повсеместно"
MySQL, MySQL is a database management system that allows you to manage relational databases. It is open source software backed by Oracle. It means you can use� MySQL for OEM/ISV. Over 2000 ISVs, OEMs, and VARs rely on MySQL as their products' embedded database to make their applications, hardware and appliances more competitive, bring them to market faster, and lower their cost of goods sold.
try like this :
SELECT * FROM videos WHERE title LIKE '% все %'
% at the beginning and the end of your search string (word).
for example. change
'% все %'.
But if you want it to be not case sensitive then
SELECT * FROM videos WHERE LOWER(title) LIKE LOWER('% все %')
What Is MySQL? & Why It Is the World's Most Popular Open Source , The data in a MySQL database are stored in tables. A table is a collection of related data, and it consists of columns and rows. Databases are useful for storing� Oracle MySQL Database Service is a fully managed database service that enables organizations to deploy cloud native applications using the world’s most popular open source database. It is 100% developed, managed, and supported by the MySQL team. Fastest availability of updates, new features, and
When you use
WHERE title LIKE 'все'
the query matches titles that are equal to
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 |
PHP: MySQL Database, Oracle MySQL Database Service is a fully managed database service that enables organizations to deploy cloud native applications using the world's most � MySQL (/ ˌ m aɪ ˌ ɛ s ˌ k juː ˈ ɛ l / "My S-Q-L") is an open-source relational database management system (RDBMS). Its name is a combination of "My", the name of co-founder Michael Widenius's daughter, and "SQL", the abbreviation for Structured Query Language.
MySQL Database Service, MySQL is the most popular Open Source Relational SQL Database Management System. MySQL is one of the best RDBMS being used for developing various� MySQL takes its name from Widenius’ daughter, named My. The MySQL project was released as open source in 2000, under the GNU General Public License (GPL). By 2001, MySQL had reached more than 2 million active installations; by 2004, the software was being downloaded more than 30,000 times a day.
MySQL Tutorial, This tutorial provides and introduction to Databases, SQL and the open source relational Duration: 11:30 Posted: Jan 22, 2019 MySQL is the most popular Open Source Relational SQL Database Management System. MySQL is one of the best RDBMS being used for developing various web-based software applications. MySQL is developed, marketed and supported by MySQL AB, which is a Swedish company.
MySQL IN 10 MINUTES (2020), Download MySQL - Multi-user and accessible SQL server that packs the most advanced database technology that was designed for speed, scalability and reliability
- First of all, could you prepare minimal reproducible example with concrete data. Did you try to use wildcard characters? For example
- Hi, How can I create reproducible database? My hosting does not allow outside connections to Database. I tried wildcards and it works, but it defeats my purpose because I need to find exact word match in strings.
- There are free public tools for it: sqlfiddle.com , db-fiddle.com , or you can put SQL DDL and a couple of inserts here. Only meaningful fields.
- If you tried wildcards and it works then it should work without them too. But why
column LIKE 'слово'and not
column = 'слово'?
- @artoodetoo - thanks for tip, added sqlfiddle.
- Wow! Thanks! I haven't seen REGEX pattern like this before. I guess this is better answer to my problem
- Thanks but that doesn't work for me, because if I include wildcards it will include results with other words which can contain this word. For example "%все%" (translates like "all") will return results with words "всегда" (translates "always") I temporarily used a workaround to this using LIKE '% все %'. (Added spaces to make sure that it searches a word) but this is workaround and I have to know what is real reason behind my problem.
- Spaces is not a silver bullet. I suppose you'd like to get word at the very begining or end of the string too, also it can be surrounded by "," and other separators. I guess the only solution is regular expression. Look at my answer.
- Yes, I came up with something similar in the end, but I bluntly added spaces in PHP when formed sql :) Your solution is much more elegant. If you don't mind I won't mark your answer for a while, I want to see if someone knows what is a real reason behind this problem. I suspect something with collation and encoding.
- @RovshanMamedov I added an explanation in my answer.
- OK, that explains it. I always thought of LIKE as a loose search inside a string. Thank you for explaining this to me. (I probably will get more downvotes for this comment, but I can't leave without thanks :)
- No, I don't believe this would cause any downvotes. Don't hesitate to ask. Just make sure that you provide all the explanations, sample data and expected results needed so the question is clear. This is the fastest way to get answers.
- @forpas how about separators different from space?