Name
BINARY
Synopsis
BINARY string
Use this function to treat strings in their binary state. This function is useful for making SQL statements case-sensitive. Notice that the syntax does not call for parentheses:
SELECT student_id, name_last FROM students WHERE BINARY LEFT(UCASE(name_last), 1) <> LEFT(name_last, 1); +------------+-----------+ | student_id | name_last | +------------+-----------+ | 433302000 | dyer | | 434016005 | de Vitto | +------------+-----------+
This SQL
statement checks for any students whose last name starts with a
lowercase letter. To do this, each student’s last
name is converted to uppercase letters and then the first letter
starting from the left is extracted to compare it to the first letter
of the last name without case conversion. The results show one record
that is probably a typing error and another (the second one) that is
probably correct. Notice that the BINARY
keyword
is specified before the comparison is made between the strings and is
applied to both strings.
Get MySQL in a Nutshell now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.