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

This works also:

SELECT LOWER(DisplayName) as DN
FROM   Bidders
WHERE  OrgID=45
HAVING DN like "cbbautos%"
LIMIT  10;

Answer 2

In this method, you do not have to select the searched field:

SELECT table.id 
FROM table
WHERE LOWER(table.aTextField) LIKE LOWER('%SearchAnything%')

Answer 3

A much better solution in terms of performance:

SELECT .... FROM .... WHERE `concatenated` LIKE BINARY '%SearchTerm%';

String comparision is case-sensitive when any of the operands is a binary string.

Another alternative is to use COLLATE,

SELECT ....
FROM ....
WHERE `concatenated` like '%SearchTerm%' COLLATE utf8_bin;

Answer 4

This is the working code:

SELECT title,description
FROM (
 SELECT title,description, LOWER(CONCAT_WS(title,description)) AS concatenated
 FROM table1 
) AS Q
WHERE concatenated LIKE LOWER('%search%') 

Answer 5

Try this:

SELECT LOWER(CONCAT_WS(title,description)) AS concatenated 
WHERE concatenated LIKE '%searchterm%'

or (to let you see the difference)

SELECT LOWER(CONCAT_WS(title,description)) AS concatenated 
WHERE concatenated LIKE LOWER('%SearchTerm%')

Answer 6

Check CHARSET mentioned in the table schema:

show create table xyz;

Based on CHARSET, you can try the following.

select name from xyz where name like '%Man%' COLLATE latin1_bin;
select name from xyz where name like '%Man%' COLLATE utf8_bin;

Following are the cases which worked for me, CHARSET=latin1, MySQL version = 5.6.

mysql> select installsrc from appuser where installsrc IS NOT NULL and installsrc like 'Promo%' collate latin1_bin limit 1;
+-----------------------+
| installsrc            |
+-----------------------+
| PromoBalance_SMS,null |
+-----------------------+
1 row in set (0.01 sec)

mysql>
mysql> select installsrc from appuser where installsrc IS NOT NULL and installsrc like 'PROMO%' collate latin1_bin limit 1;
+---------------------------+
| installsrc                |
+---------------------------+
| PROMO_SMS_MISSEDCALL,null |
+---------------------------+
1 row in set (0.00 sec)

mysql> select installsrc from appuser where installsrc IS NOT NULL and installsrc like 'PROMO%' limit 1;
+-----------------------+
| installsrc            |
+-----------------------+
| PromoBalance_SMS,null |
+-----------------------+
1 row in set (0.01 sec)