Working with Strings - MySQL - page 154

This problem occurs for strings that have a BINARY or BLOB data type. These are binary
strings that have no character set or collation. Lettercase does not apply, and UPPER()
and LOWER() do nothing.
To map a binary string to a given lettercase, convert it to a nonbinary string, choosing
a character set that has uppercase and lowercase characters. The case-conversion func‐
tions then work as you expect because the collation provides case mapping:
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 example uses a table column, but the same principles apply to binary string literals
and string expressions.
If you're not sure whether a string expression is binary or nonbinary, use the CHAR
SET() function to find out; see Recipe 5.5 .
To convert the lettercase of only part of a string, break it into pieces, convert the relevant
piece, and put the pieces back together. Suppose that you want to convert only the initial
character of a string to uppercase. The following expression accomplishes that:
CONCAT(UPPER(LEFT( str ,1)),MID( str ,2))
But it's ugly to write an expression like that each time you need it. For convenience,
define a stored function:
mysql> CREATE FUNCTION initial_cap (s VARCHAR(255))
-> RETURNS VARCHAR(255) DETERMINISTIC
-> RETURN CONCAT(UPPER(LEFT(s,1)),MID(s,2));
Then you can capitalize initial characters more easily:
mysql> SELECT thing, initial_cap(thing) FROM limbs;
+--------------+--------------------+
| thing | initial_cap(thing) |
+--------------+--------------------+
| human | Human |
| insect | Insect |
| squid | Squid |
| fish | Fish |
| centipede | Centipede |
| table | Table |
| armchair | Armchair |
| phonograph | Phonograph |
| tripod | Tripod |
| Peg Leg Pete | Peg Leg Pete |