MySQL case-sensitive LIKE search
When searching for partial strings in MySQL with LIKE you will match case-insensitive by default*.
SELECT name FROM users WHERE name LIKE 't%'
+--------------------+
| name |
+--------------------+
| Test |
| test |
+--------------------+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.
SELECT name FROM users WHERE name LIKE BINARY 't%'
+--------------------+
| name |
+--------------------+
| test |
+--------------------+* Assuming default character set and collation. The primary factor in determining case-sensitivity in this case is the collation of the column being searched (see Case Sensitivity in String Searches). (Thanks Nick N.)
Like to comment? Feel free to send me an email or reach out on Twitter.
Did this or another article help you? If you like and can afford it, you can buy me a coffee (3 EUR) ☕️ to support me in writing more posts. In case you would like to contribute more or I helped you directly via email or coding/troubleshooting session, you can opt to give a higher amount through the following links or adjust the quantity: 50 EUR, 100 EUR, 500 EUR. All links redirect to Stripe.