40

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?

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Pmc Machinery
  • 450
  • 2
  • 5
  • 8
  • 3
    the relevant manual page (for 1 version!) http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html – seanv507 Oct 08 '14 at 15:40

8 Answers8

53

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;
kolypto
  • 31,774
  • 17
  • 105
  • 99
  • 4
    I think he wants the searches case INsensitive. – Fuzzy76 Jun 27 '13 at 09:49
  • 11
    No BINARY search is case SENSITIVE. BINARY means it is comparing the binary values of each character, rather than the letters they represent. In non-binary comparison "A" = "a", where as in binary comparison "A" != "a" because 65 != 97. You have this backwards. This IS however useful for those that are looking for case-sensitive searches. – Jouva Moufette Oct 06 '13 at 18:53
  • 2
    Just tried: `SELECT 'a' LIKE 'A'; -- 1` ; `SELECT 'a' LIKE BINARY 'A'; -- 0`. So binary search is case insensitive. Objections? – kolypto Oct 08 '13 at 13:18
  • 3
    Binary REALLY shouldn't be case insensitive. I suspect there's something wrong with your test case if you're seeing case insensitive results. Though I wouldn't put it past the MySQL devs to do something this depraved, I guess. – Winfield Trail Dec 17 '13 at 02:54
  • 2
    @kolypto Why does your answer say "string comparison is case-sensitive" and the comments here say "binary search is case insensitive"? Binary search is clearly sensitive, as your example shows. – Naltharial May 06 '14 at 00:04
  • 1
    @Naltharial, :) sure, just confused the terms. Binary strings make comparisons case-sensitive, that's for sure. Sorry – kolypto May 06 '14 at 14:05
20

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%')
Marco
  • 56,740
  • 14
  • 129
  • 152
  • Tried that, still case sensitive. – Pmc Machinery Nov 10 '11 at 17:11
  • @PmcMachinery: did you see that I changed your LIKE part with a lowercase string? – Marco Nov 10 '11 at 17:13
  • Yes, thanks, I did. When I do this, I can find uppercase "SearchTerms" too. It doesn't seem that LOWER is doing anything. – Pmc Machinery Nov 10 '11 at 17:19
  • 6
    I figured it out. Seems that concat_ws converts the result to a BLOB. Which UPPER and LOWER doesn't work on. I fixed this by adding CONVERT( ... USING utf8) to convert the BLOB to a UTF8 string and then run the LOWER function on that. SELECT LOWER(CONVERT(CONCAT_WS(title,description) USING utf8) AS concatenated WHERE concatenated LIKE '%searchterm%' – Pmc Machinery Nov 10 '11 at 17:27
6

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

SELECT table.id 
FROM table
WHERE LOWER(table.aTextField) LIKE LOWER('%SearchAnything%')
Malkocoglu
  • 467
  • 3
  • 10
  • 3
    This answer was first posted almost two years ago... there's no point doing it again. – Ben Sep 08 '13 at 17:17
1

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)
Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
minhas23
  • 9,291
  • 3
  • 58
  • 40
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%';
Cédric Françoys
  • 870
  • 1
  • 11
  • 22
-1

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.

neophyte
  • 6,540
  • 2
  • 28
  • 43
-2

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%')
Andrey Soldatov
  • 1
-2

This works also:

SELECT LOWER(DisplayName) as DN
FROM   Bidders
WHERE  OrgID=45
HAVING DN like "cbbautos%"
LIMIT  10;
fthiella
  • 48,073
  • 15
  • 90
  • 106
david
  • 1