case sensitive match in mysql ...

[Anon]

hi!
can some one tell me how do i do a case sensitive match in mysql ....

eg: column name = userid;
value = "DANNY";
column name = usercode;
value = 100;
if i say ...

select usercode from usertab where userid='danny';

this query returs true .. i,e(usercode=100)
where as i want this to return false ... i,e case sensitive match ...
can some one tell me how to do it ...

thanks,
danny ....

[Anon]

change the column type to include the BINARY keyword will make all comparisons on that column case sensitive. e.g.:

if the column is now defined as

userid varchar(32), change it to:

userid varchar(32) binary

You can do this with the ALTER TABLE statement. Don't worry, your data will be preserved.

[Anon]

Or you can do it in the select statement with the BINARY keyword:

select usercode from usertab where BINARY userid='danny';

From the manual:

The BINARY operator casts the string following it to a binary string. This is an easy way to force a column comparison to be case sensitive even if the column isn't defined as BINARY or BLOB:
mysql> select "a" = "A";
-> 1
mysql> select BINARY "a" = "A";
-> 0

BINARY was introduced in MySQL Version 3.23.0. Note that in some context MySQL will not be able to use the index efficiently when you cast an indexed column to BINARY.

---John Holmes...