MySQL Cookbook, 2nd Edition

Converting the Lettercase of a String

Problem

You want to convert a string to uppercase or lowercase.

Solution

Use the UPPER() or LOWER() function. If they don’t work, see Converting the Lettercase of a Stubborn String.

Discussion

The UPPER() and LOWER() functions convert the lettercase of a string:

mysql>SELECT thing, UPPER(thing), LOWER(thing) FROM limbs;
+--------------+--------------+--------------+
| thing        | UPPER(thing) | LOWER(thing) |
+--------------+--------------+--------------+
| human        | HUMAN        | human        |
| insect       | INSECT       | insect       |
| squid        | SQUID        | squid        |
| octopus      | OCTOPUS      | octopus      |
| fish         | FISH         | fish         |
| centipede    | CENTIPEDE    | centipede    |
| table        | TABLE        | table        |
| armchair     | ARMCHAIR     | armchair     |
| phonograph   | PHONOGRAPH   | phonograph   |
| tripod       | TRIPOD       | tripod       |
| Peg Leg Pete | PEG LEG PETE | peg leg pete |
| space alien  | SPACE ALIEN  | space alien  |
+--------------+--------------+--------------+

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));

You can then capitalize ...