« MySQL Pop Quiz #20 | Main | SUBSTRING() :: MySQL Function of the Day »
BINARY :: MySQL Function of the Day
By Carsten | March 27, 2008
Function name: BINARY
Aliases: CAST(… AS BINARY)
Function type: String function
Purpose: force a string to be regarded as binary sequence
Description: BINARY is actually a shorthand operator for the CAST() function, more specifically CAST(… AS BINARY).
During normal operation, using a character set such as Latin-1, comparisons are done irregardles of case so ‘bitbybit’ is regarded equal to ‘BiTbYbIt’ and so forth. More subtly, certain characters are regarded equal even when using accents: ‘a’ compares equal to ‘á’, ‘À’, ‘ã’, etc.
Using the BINARY operator, you can force distinct letters, accents and cases to be compared as distinct values:
SELECT BINARY 'a' = 'A' => 0 SELECT BINARY 'a' = 'á' => 0
Note that BINARY is a unary operator, not a comparison operator, and works directly on the individual string. In the preceding example, we’re asking for the result of comparing the BINARY ‘a’ to the non-BINARY ‘á’. The reason that it still works is due to the general comparison rules within MySQL, which dictate that if any of the operands are BINARY, then the expression should be evaluated as if all operands are BINARY.
MySQL manual entry on BINARY
MySQL manual entry mentioning BINARY comparison rules
[fadps]
Topics: MySQL Function of the day |
April 21st, 2008 at 12:14 pm
[...] Thursday, March 27th, 2008: BINARY [...]