MySQL case sensitive query

mysql case-sensitive
Michael Liao picture Michael Liao · Oct 22, 2011 · Viewed 97.9k times · Source

This has been asked on this site before but I couldn't find a sufficient answer. If I'm doing a query like:

Select Seller from Table where Location = 'San Jose'

How can I make it return only Sellers with Location 'San Jose' instead of 'san jose' or something else?

Answer

James mason picture James mason · Oct 22, 2011

MySQL queries are not case-sensitive by default. Following is a simple query that is looking for 'value'. However it will return 'VALUE', 'value', 'VaLuE', etc…

SELECT * FROM `table` WHERE `column` = 'value'

The good news is that if you need to make a case-sensitive query, it is very easy to do using the BINARY operator, which forces a byte by byte comparison:

SELECT * FROM `table` WHERE BINARY `column` = 'value'

Related questions

How can I make SQL case sensitive string comparison on MySQL?

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?

Read More
Are table names in MySQL case sensitive?

Are table names in MySQL case sensitive? On my Windows development machine the code I have is able to query my tables which appear to be all lowercase. When I deploy to the test server in our datacenter the table …

Read More
How to force case sensitive table names?

I'm working on a MySQL database in windows. I must move it into a Linux environment. MySQL database contains a lot of tables and stored procedures which are CASE SENSITIVE. When I backup my database, all table names are forced …

Read More