Menu
When searching for partial strings in MySQL with LIKE you will match case-insensitive by default*. 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 .
Table names are stored in lowercase on disk and name comparisons are not case-sensitive. MySQL converts all table names to lowercase on storage and lookup. This behavior also applies to database names and table aliases.
In the search grid, choose tables and views of interest or leave them all checked. To narrow down the MySQL search data scope, select the table, views, numeric, text type, and date columns checkboxes. To start the search, click the Find button or hit the Enter key from the keyboard.
If your searches on that field are always going to be accent-sensitive, then declare the collation of the field as utf8_bin (that'll compare for equality the utf8-encoded bytes) or use a language specific collation that distinguish between the accented and un-accented characters.
col_name varchar(10) collate utf8_bin
If searches are normally accent-insensitive, but you want to make an exception for this search, try;
WHERE col_name = 'abád' collate utf8_bin
In my version (MySql 5.0), there is not available any utf8 charset collate for case insensitive, accent sensitive searches. The only accent sensitive collate for utf8 is utf8_bin. However it is also case sensitive.
My work around has been to use something like this:
SELECT * FROM `words` WHERE LOWER(column) = LOWER('aBád') COLLATE utf8_bin
The MySQL bug, for future reference, is http://bugs.mysql.com/bug.php?id=19567.
Accepted answer is good, but beware that you may have to use COLLATE utf8mb4_bin instead!
WHERE col_name = 'abád' collate utf8mb4_bin
Above fixes errors like:
MySQL said: Documentation 1253 - COLLATION 'utf8_bin' is not valid for CHARACTER SET 'utf8mb4'
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With