Converting the Lettercase of a “Stubborn” String
Problem
You want
to convert a string to uppercase or lowercase, but
UPPER()
and LOWER()
don’t work.
Solution
You’re probably trying to convert a binary string. Convert it to a nonbinary string so that it has a character set and collation and becomes subject to case mapping.
Discussion
The usual way to convert a string to uppercase or lowercase is
to use the UPPER()
or
LOWER()
function:
mysql>SET @s = 'aBcD';
mysql>SELECT UPPER(@s), LOWER(@s);
+-----------+-----------+ | UPPER(@s) | LOWER(@s) | +-----------+-----------+ | ABCD | abcd | +-----------+-----------+
But sometimes you’ll run across a string that is
“stubborn” and resists lettercase conversion. This is
common for columns that have a
BINARY
or BLOB
data type:
mysql>CREATE TABLE t (b BLOB) SELECT 'aBcD' AS b;
mysql>SELECT b, UPPER(b), LOWER(b) FROM t;
+------+----------+----------+ | b | UPPER(b) | LOWER(b) | +------+----------+----------+ | aBcD | aBcD | aBcD | +------+----------+----------+
The cause of the problem here is that the column is a binary
string: it has no character set or collation and lettercase does not
apply. Thus, UPPER()
and
LOWER()
do nothing, which can
be confusing. Compounding the confusion is that lettercase conversion
of binary strings used to work in older versions
of MySQL, but does so no longer. What’s going on? Here is the
history:
Before MySQL 4.1, all strings, including binary strings, were interpreted with respect to the server’s default character ...
Get MySQL Cookbook, 2nd Edition 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.