SQL case-sensitive comparison over MySql
If someone is finding a way to do a case sensitive comparison in SQL (over MySql), below can find the recipe:
This one produces a True result:
1 | SELECT 'A' = 'a'; |
The solution is doing the comparison with a = or LIKE and the BINARY clause:
2 | SELECT 'A' LIKE BINARY 'a'; |
or
3 | SELECT 'A' = BINARY 'a'; |
This produces a False result (good for case sensitive comparisons)
And be careful because this:
4 | SELECT 'A' LIKE 'a'; |
produces a True result.
Reader Comments
Thx for this.
SELECT \’A\’ = BINARY \’a\’ does it too, you dont need LIKE everytime.
Hi ibsi, yes when I used it was with the LIKE clause and put it in the post and forgot the normal clause witout the LIKE. Thanks for the comment
Great point and very interesting food for thought. I’m not sure I have any clients I can replicate this with, but will bear in mind for the future. Regards