MySQL Cookbook, 2nd Edition

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 ...