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.
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
set. Consequently, the UPPER()
and LOWER()
functions performed case
mapping even for binary strings:
mysql>SET @s = BINARY 'aBcD';
mysql>SELECT @s, LOWER(@s), UPPER(@s);
+------+-----------+-----------+ | @s | LOWER(@s) | UPPER(@s) | +------+-----------+-----------+ | aBcD | abcd | ABCD | +------+-----------+-----------+
In MySQL 4.1, character set handling was revised
significantly, with one of the changes being that character set
and collation applied only to nonbinary strings. From 4.1 up, a
binary string is just a sequence of bytes, and lettercase has no
meaning, even if you store what looks like text in the string. As
a result, the LOWER()
and UPPER()
functions do nothing when
applied to binary strings:
mysql>SET @s = BINARY 'aBcD';
mysql>SELECT @s, LOWER(@s), UPPER(@s);
+------+-----------+-----------+ | @s | LOWER(@s) | UPPER(@s) | +------+-----------+-----------+ | aBcD | aBcD | aBcD | +------+-----------+-----------+
To map a binary string to a given lettercase, convert it to a
nonbinary string, choosing a character set that contains an alphabet
with uppercase and lowercase characters. The case-conversion functions
then will work as you expect because the collation provides case
mapping. The following example uses the BLOB
column from earlier in this section,
but the same principles apply to binary string literals and string
expressions:
mysql>SELECT b,
->UPPER(CONVERT(b USING latin1)) AS upper,
->LOWER(CONVERT(b USING latin1)) AS lower
->FROM t;
+------+-------+-------+ | b | upper | lower | +------+-------+-------+ | aBcD | ABCD | abcd | +------+-------+-------+
The same kind of case-conversion problem occurs with functions
that return binary strings, which is typical for functions such
as
MD5()
or
COMPRESS()
that perform
encryption or compression.
If you’re not sure whether a string expression is binary or
nonbinary, use the CHARSET()
function. The following
example shows that VERSION()
returns a nonbinary string, but MD5()
returns a binary string:
mysql>SELECT CHARSET(VERSION()), CHARSET(MD5('some string'));
+--------------------+-----------------------------+
| CHARSET(VERSION()) | CHARSET(MD5('some string')) |
+--------------------+-----------------------------+
| utf8 | binary |
+--------------------+-----------------------------+
That result indicates that the string produced by VERSION()
can be case-mapped directly,
but the string produced by MD5()
must first be converted to a
nonbinary string:
mysql>SELECT UPPER(VERSION());
+------------------+ | UPPER(VERSION()) | +------------------+ | 5.1.12-BETA-LOG | +------------------+ mysql>SELECT UPPER(CONVERT(MD5('some string') USING latin1));
+-------------------------------------------------+ | UPPER(CONVERT(MD5('some string') USING latin1)) | +-------------------------------------------------+ | 5AC749FBEEC93607FC28D666BE85E73A | +-------------------------------------------------+
3.14.131.212