Menu
I have a function that returns five characters with mixed case. If I do a query on this string it will return the value regardless of case.
How can I make MySQL string queries case sensitive?
249
When searching for partial strings in MySQL with LIKE you will match case-insensitive by default*. If you want to match case-sensitive, you can cast the value as binary and then do a byte-by-byte comparision vs. a character-by-character comparision. The only thing you need to add to your query is BINARY .
A string comparison is case sensitive when you don't want it to be, or vice versa.
Table names are stored in lowercase on disk and name comparisons are not case-sensitive. MySQL converts all table names to lowercase on storage and lookup.
The good news is that if you need to make a case-sensitive query, it is very easy to do:
SELECT * FROM `table` WHERE BINARY `column` = 'value' http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html
The default character set and collation are latin1 and latin1_swedish_ci, so nonbinary string comparisons are case insensitive by default. This means that if you search with col_name LIKE 'a%', you get all column values that start with A or a. To make this search case sensitive, make sure that one of the operands has a case sensitive or binary collation. For example, if you are comparing a column and a string that both have the latin1 character set, you can use the COLLATE operator to cause either operand to have the latin1_general_cs or latin1_bin collation:
col_name COLLATE latin1_general_cs LIKE 'a%' col_name LIKE 'a%' COLLATE latin1_general_cs col_name COLLATE latin1_bin LIKE 'a%' col_name LIKE 'a%' COLLATE latin1_bin If you want a column always to be treated in case-sensitive fashion, declare it with a case sensitive or binary collation.
40
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
