Working with Cases of Strings

(Page 1 of 4 )

In this third part of a series of articles that explain how to work with strings and string data, you'll learn about cases and case sensitivity. It is excerpted from chapter five of the MySQL Cookbook, Second Edition, written by Paul DuBois (O'Reilly; ISBN: 059652708X). Copyright © 2006 O'Reilly Media, Inc. All rights reserved. Used with permission from the publisher. Available from booksellers or direct from O'Reilly Media.

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 Recipe 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  ARMCHAIRarmchair

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

Converting the Lettercase of a String

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.

Next: 5.8 Converting the Lettercase of a Stubborn String >>
 

More Database Articles Articles
More By O'Reilly Media

blog comments powered by Disqus