Relative search in Mysql-CodePudding

In my database I have a record with title is "Học lập trình hướng đối tượng với Java"

When I search I use SELECT * FROM posts WHERE post_title like '%hoc%'

Or SELECT * FROM posts WHERE post_title like '%Học%'

But it doesn't work

How can I fix it?

CodePudding user response：

The like operator is case-sensitive, so if the case of the text you are searching for does not match the case of the text in the post_title column, the search will not return any results. You can use the LOWER() or UPPER() function to convert the text in the post_title column to lowercase or uppercase, respectively, before performing the search. For example:

select * from posts WHERE UPPER(post_title) like '%HỌC%';

select * from posts WHERE LOWER(post_title) like '%học%';

select * from posts WHERE post_title like '%Học%';

Working Demo: https://dbfiddle.uk/-fJrbLMk

CodePudding user response：

The issue here is the character ọ in the database and the character ọ which you have used in the query are different even though they looks same.

The original character in the database is a combination of two characters, Latin small letter o (U 006F) and a Combining Dot Below ̣ (U 0323) character to form ọ. On the other hand, what you have used in the query is a single character, Latin Small Letter O With Dot Below (U 1ECD).

You can fix the issue by using the same combination of characters as of the database:

SELECT * FROM posts WHERE post_title like '%Học%'

or you can use _ or % to skip unknown characters:

SELECT * FROM posts WHERE post_title like '%H__c%'
SELECT * FROM posts WHERE post_title like '%H%c%'