Like Case Sensitive in MySQL

Question

I have a MySQL query:

SELECT concat_ws(title,description) as concatenated HAVING concatenated LIKE '%SearchTerm%';

And my table is encoded utf8_general_ci with MyISAM.

Searches seem to be case sensitive.

I can't figure out how to fix it. What's going wrong and/or how do I fix it?

Answer 1

Just for completion, in case it helps:

As stated on https://dev.mysql.com/doc/refman/5.7/en/case-sensitivity.html, for default character sets, nonbinary string comparisons are case insensitive by default.

Therefore, an easy way to perform case-insensitive comparisons is to cast the field to CHAR, VARCHAR or TEXT type.

Here is an example with a check against a single field:

SELECT * FROM table1 WHERE CAST(`field1` AS CHAR) LIKE '%needle%';

Answer 2

This problem is occurring in this case because of the collation used in the table. You have used utf8_general_ci as collation. If the collation is changed to utf8_general_ci then the searches will not be case sensitive. So, one possible solution is to change the collation.