Case Sensitive string search on MySQL

Quick reference post to search string with case sensitivity.

MySQL String search is case-insensitive by default. Using BINARY on the WHERE clause forces MySQL to check its binary Collation which tries to match its character code rather than its string value; meaning “A != a” when using BINARY.

SELECT * FROM TABLE WHERE BINARY column = 'ABcdEFG';

The BINARY operator casts the string following it to a binary string. This is an easy way to force a comparison to be done byte by byte rather than character by character. BINARY also causes trailing spaces to be significant.

Reference: http://dev.mysql.com/doc/refman/5.0/en/charset-binary-op.html

random number in MySQL with range

I am creating a prototype that needs to simulate adding IDs from 1-7. Since i’m lazy i updated the new column with a random numnber.

SELECT FLOOR(RAND()*7)+1

MySQL has a rand() function which will give you a floating point, multiply it by the range you need and add 1.

Note: this post serves as a note for future reference