Case Sensitive in MySQL searches
September 13th, 2007
Interesting problem I encountered this morning. I needed to make a case sensitive search through MySQL. So consider this search.
SELECT * FROM people WHERE field LIKE '%Derek%'
This will match
- Derek;
- derek; and
- DEREK
but I only was interested in exact matches. Ah right! MySQL cases are case insensitive by default. Hmm… how to get around this one?
So begin the search… which brought me to a MySQL docs page called Case Sensitivity in Searches. Buried in that page is this little gem.
If you want to make this search case sensitive, make sure that one of the operands has a case sensitive or binary collation.
But then all their examples suggest changing the collation with the “COLLATE” argument. Then something from years gone by clicked in my head, and I remembered forcing the result to binary, which led me to write this query.
SELECT * FROM people WHERE BINARY field LIKE '%Derek%'
Works a charm, so I wanted to document it (for my benefit), and share it on blog (for anyone reading this).
And now that I’ve got my result, this pretty much ends my investigation into this, so if anyone cares to shed more light, or share a “proper” way of doing this, please just comment below!
This entry was made on September 13th, 2007 @ 10:05 and filed into PHP.
Michael Wales wrote on September 13th, 2007 @ 12:51
Using the COLLATE clause is the preferred way to perform case sensitive comparisons.
See the MySQL Documentation, although BINARY is an acceptable way to perform this function I think it may be a bit more intensive as it’s performing a byte-by-byte comparison of the strings.