Recipe 5.7. 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 Section 5.8.

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 initial characters more easily like this:

mysql> SELECT thing, initial_cap(thing) FROM limbs; +--------------+--------------------+ | thing        | initial_cap(thing) | +--------------+--------------------+ | human        | Human              | | insect       | Insect             | | squid        | Squid              | | octopus      | Octopus            | | fish         | Fish               | | centipede    | Centipede          | | table        | Table              | | armchair     | Armchair           | | phonograph   | Phonograph         | | tripod       | Tripod             | | Peg Leg Pete | Peg Leg Pete       | | space alien  | Space alien        | +--------------+--------------------+ 

For more information about writing stored functions, see Chapter 16.