Case Sensitive MySQL Queries

21 March

Case Sensitive MySQL Queries

While building a site around MySQL, there may come a time when you want to make a query that is case sensitive. You get down to coding, only to realize that no matter how hard you try, everything still matches!

The reason you can't do a case sensitive query is because most databases will automatically set a case insensitive collation for all of your tables. The only way you can fix this is to either change the table collation (which makes everything case sensitive), or you can cast your query like this:

SELECT * FROM [tablename] WHERE [fieldname] = BINARY 'Hello World'

This will match 'Hello World' but will not match 'hello world'.

If you're not feeling up to the challenge of changing the query on your own (or you're feeling lazy) then if you have phpMyAdmin, you can do the following:

  1. Click on your table in the left-hand column
  2. Along the top, click on the Operations tab
  3. You should see a "Collation" section. From the dropdown, select a Collation that does not have _ci at the end (latin1_bin should work).

TAGS: General