How to use MySQL for case sensitive string comparison

By default MYSQL character set and collation are latin1 and latin1_swedish_ci, so non-binary string comparisons are case insensitive. 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 as 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:

i.e,

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

[code]
col_name COLLATE latin1_general_cs LIKE ‘value%’
col_name LIKE ‘value%’ COLLATE latin1_general_cs
col_name COLLATE latin1_bin LIKE ‘a%’
col_name LIKE ‘value%’ COLLATE latin1_bin
BINARY col_name = ‘value’
[/code]

Your SQL Query will be look like this to make a case-sensitive query

[code]
SELECT * FROM table_name WHERE BINARY column_name = ‘value’;

SELECT * FROM table_name WHERE col_name COLLATE latin1_general_cs LIKE ‘value%’;

SELECT * FROM table_name WHERE col_name LIKE ‘value%’ COLLATE latin1_general_cs;
[/code]

If you want a column always to be treated in case-sensitive manner, then declare it with a case sensitive or binary collation while creating your table.

For Reference:
http://dev.mysql.com/doc/refman/5.7/en/case-sensitivity.html

Permanent link to this article: https://blog.openshell.in/2014/07/how-to-use-mysql-for-case-sensitive-string-comparison/

Leave a Reply

Your email address will not be published.