Additional languages
« B.1.5 Query-Related Issues
B.1.5.2 Problems Using DATE Columns »
Section Navigation      [Toggle]

B.1.5.1. Case Sensitivity in Searches

By default, MySQL searches are not case sensitive (although there are some character sets that are never case insensitive, such as czech). This means that if you search with col_name LIKE 'a%', you get all column values that start with A or a. If you want to make this search case sensitive, make sure that one of the operands has a case sensitive or binary collation. For example, if you are comparing a column and a string that both have the latin1 character set, you can use the COLLATE operator to cause either operand to have the latin1_general_cs or latin1_bin collation. For example: 

col_name COLLATE latin1_general_cs LIKE 'a%'
col_name LIKE 'a%' COLLATE latin1_general_cs
col_name COLLATE latin1_bin LIKE 'a%'
col_name LIKE 'a%' COLLATE latin1_bin

If you want a column always to be treated in case-sensitive fashion, declare it with a case sensitive or binary collation. See Section 13.1.8, “CREATE TABLE Syntax”.

Simple comparison operations (>=, >, =, <, <=, sorting, and grouping) are based on each character's “sort value.” Characters with the same sort value (such as ‘E’, ‘e’, and ‘é’) are treated as the same character.


User Comments

Posted by Lucio Zambon on January 8 2004 2:51am[Delete] [Edit]

The expression STRCMP(col_name, "A") = 0 is case-sensitive
only before MySQL 4.0, for MySQL 4.0 or later use BINARY. See Manual: 6.3.2.1 String Comparison Functions

Posted by Allann Jones on February 15 2004 7:48am[Delete] [Edit]

For MySQL 4.1 and later you can use binary collates:

create table dictionary (
ortography varchar(255) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
);

ALTER TABLE dictionary ADD UNIQUE INDEX (ortography);

Now, it's possible to insert as `ortography`:

São, sao, Sao, sAo, saO, SAO, etc...

Posted by [name withheld] on July 16 2004 12:22pm[Delete] [Edit]

for IN operation use binary

select * from table where binary field in (...);

interesting is that syntax

select * from table where field in (binary 'value1') doesn't give error message but doesn't work correct either.

Posted by Carl Furst on September 8 2005 3:45pm[Delete] [Edit]

Strings returned from DECODE are case sensitive for 4.0, probably because they're not really strings, they're blobs. However using "LIKE BINARY" keywords had no effect.

For example

SELECT COUNT(*) FROM EMAIL_TABLE WHERE DECODE(EMAIL, 'pass_phr') LIKE '%AOL.COM';

Will only yield the number of emails that have 'AOL.COM' at the end.

SELECT COUNT(*) FROM EMAIL_TABLE WHERE DECODE(EMAIL, 'pass_phr') LIKE '%aol.com';

will only yield the number of emails that have 'aol.com' at the end.

The same results were obtained using "LIKE BINARY" keyword. The email field was a tinyblob type.

Posted by Christine Bacinski on February 28 2006 7:26pm[Delete] [Edit]

I've found the Hex() function to be somewhat useful in case-sensitive searches.

Posted by Ed Cooley on May 15 2006 1:27pm[Delete] [Edit]

SELECT * FROM mydatabase WHERE sendmail_id = BINARY 'k4912Nva000697'
(found set NvA000 vs Nva000)
w/o BINARY, both are found.

This allows me to do case-sensitive queries, without modifying the structure of the database. To make the query 'case sensitive' by default, use BINARY in the creation of the table.

http://dev.mysql.com/doc/refman/4.1/en/create-table.html

| VARCHAR(length) [BINARY]
<cut>
| LONGBLOB
| TINYTEXT [BINARY]
| TEXT [BINARY]
| MEDIUMTEXT [BINARY]

Finally, case-sensitive searches, if you can use them, are faster than case-insensitive (on unindexed fields).

Posted by David Schooley on September 21 2006 3:10pm[Delete] [Edit]

Odd, I don't remember it working like this. In any case, I'm fairly sure that this used to work like:

WHERE `password` = 'pasSworD'

...would return nothing if the password was 'password' (lower-case). But I noticed that it accepts the above and returns the record back: case-insensitivety. Now, I use this to get it to work:

WHERE `password` COLLATE latin1_general_cs = 'pasSworD'

...which returns nothing if the password is 'password'. Perhaps it's the way my table is set up. Hope this helps anyone else who is confused about it.

Add your own comment.