How do you force mysql LIKE to be case sensitive?

Question

Possible Duplicate:
Mysql Like Case Sensitive

Mysql ignores case for its LIKE comparisons.

How can you force it to perform case-sensitive LIKE comparisons?

I won't downvote or close-vote as I can't actually find anything worthy of those votes. I do however agree with Andy, the answer could have been found more quickly with a little web search. I would call it "laziness", but 61.3k rep doesn't say lazy. Perhaps overflow-holic — James Webster, Dec 22 '12 at 23:32
@JamesWebster OK, fess up time. I'm trying to get the "answer your own question" hat. Lame, and yes SO-holic. If it doesn't score a vote within a couple of minutes, I'll close it :) even so, I thought the question had merit — Bohemian, Dec 22 '12 at 23:38
@AndyRay five years later, using Google brings up this answer first :) — Kip, Jan 26 '18 at 18:21
Um, this is not a duplicate of the linked question. The linked question is poorly titled but it is somebody trying to get a like to be *case INsensitive* and having trouble due to the behavior of `CONCAT_WS`. — Paul Wheeler, May 23 '19 at 22:06

score 208 · Accepted Answer · answered Dec 22 '12 at 23:32

208

Use LIKE BINARY:

mysql> SELECT 'abc' LIKE 'ABC';
    -> 1
mysql> SELECT 'abc' LIKE BINARY 'ABC';
    -> 0

answered Dec 22 '12 at 23:32

Bohemian

@Weltkind Why is it better? (Not saying I agree or disagree, just curious why you say that) – Kip Jan 26 '18 at 18:18
3

@Kip it's better IMHO because it doesn't presuppose any charset and works with any collation. It just does a straightforward "exact match" which everybody understands. It's also easier to remember (just remember one keyword: `BINARY`) and easier code (you don't have to remember valid charset names like `utf8_bin`) – Bohemian Jan 26 '18 at 18:55
A bit out of topic, but If you're learning SQL injection (specifically blind-based injection attacks) then I guess this is the only way to perform case sensitive comparison; things like COLLATE will mess up the query due to presence of double quotes. – Mayank Sharma Jan 04 '19 at 16:10
1

@Bohemian Unfortunately the code may be simpler, but it will be incorrect when you have accents and the column character set is different than the current session character set (usually the server default). For example if you have a column with a charset of `latin1` and you have a default server charset of `utf8mb4` then `select * from Table where Col like binary 'über'` will fail to match a row in `Table` with a `Col` value of `'über'` because ü in latin1 is `0xFC` where as in utf8mb4 it is `0xC3Bc`. If you want go for correctness you need to use a string comparison with a binary collation. – Paul Wheeler May 23 '19 at 21:46

score 38 · Answer 2 · edited Dec 23 '12 at 03:22

38

Another alternative is to use COLLATE,

SELECT *
FROM table1
WHERE columnName like 'a%' COLLATE utf8_bin;

edited Dec 23 '12 at 03:22

Bohemian

answered Dec 23 '12 at 01:24

John Woo

1

Nice. This opens up opportunity to do a custom match using a custom collation (although completely non portable, still if you need it, you need it – Bohemian Dec 23 '12 at 01:31
2

Doesn't work. The results are case insensitive – Nick Jul 23 '14 at 16:28
@Nick it is working for me. `WHERE Prompt LIKE '%ElasticSearch%' COLLATE utf8_bin` is returning `ElasticSearch` but not `Elasticsearch` (difference in casing of `S`). – Kip Jan 26 '18 at 18:18
2

This will be broken on servers where the default character set is not utf8. Also utf8mb4 is now the preferred character set. I suggest changing the code to: `like convert('a%' using utf8mb4) collate utf8mb4_bin` this will work regardless of the default character set for the server or the current character set for the session. Alternately you can explicitly set the character set for the session. – Paul Wheeler May 23 '19 at 21:34

2 Answers2