Upper case and lower case strings with MySQL

MySQL, as with other database servers, has a variety of text functions including functions for converting a string to upper case or to lower case. This post looks at how to convert a string to lower case or upper case with MySQL.

For the examples used in this post we have a simple table (called mytable) with a auto incremental primary key (mytable_id) and a name field (name). The SQL to create this table is as follows:

CREATE TABLE `mytable` (
  `mytable_id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(50) NOT NULL
)
ENGINE = myisam;

Then it’s populated with the following data (the quasi Latin text comes from lipsum.com):

+------------+----------------------------+
| mytable_id | name                       |
+------------+----------------------------+
|          1 | Lorem ipsum dolor sit amet |
|          2 | Ut purus est               |
|          3 | Leo sed condimentum semper |
|          4 | Donec velit neque          |
|          5 | Maecenas ullamcorper       |
+------------+----------------------------+

To select all the data from the above table and convert it to lower case you would do this:

SELECT LOWER(name) FROM mytable

This would return this:

+----------------------------+
| LOWER(name)                |
+----------------------------+
| lorem ipsum dolor sit amet |
| ut purus est               |
| leo sed condimentum semper |
| donec velit neque          |
| maecenas ullamcorper       |
+----------------------------+

Note that you can use either LOWER() or LCASE(). LCASE is a synonym for the LOWER() function.

Doing the above but to convert the strings to upper case, we would do this, again noting that it can be done with either UPPER() or UCASE():

SELECT UPPER(name) FROM mytable

And the resulting data:

+----------------------------+
| UPPER(name)                |
+----------------------------+
| LOREM IPSUM DOLOR SIT AMET |
| UT PURUS EST               |
| LEO SED CONDIMENTUM SEMPER |
| DONEC VELIT NEQUE          |
| MAECENAS ULLAMCORPER       |
+----------------------------+

If you wanted to convert all the values in the name column to lower case or upper case permanantly, you could do this:

UPDATE mytable SET name = LOWER(name);
UPDATE mytable SET name = UPPER(name);

There are a variety of text functions in MySQL and in this post I looked at the UPPER/UCASE and LOWER/LCASE functions for converting strings to upper and lower case. I will look at other MySQL text functions in future posts.

Check Out These Related posts: