How to Convert Lowercase Characters to Uppercase in MySQL

In MySQL, you can use the UPPER() function to convert any lowercase characters to uppercase. Alternatively, you can use the UCASE() function, which is a synonym for UPPER().

The syntax goes like this:

UPPER(str)

Or…

UCASE(str)

Where str is the string you want converted to uppercase.

Example

Here’s an example:

SELECT UPPER('homer');

Result:

+----------------+
| UPPER('homer') |
+----------------+
| HOMER          |
+----------------+

If the string already contains any uppercase characters, then they will remain uppercase.

Example:

SELECT UPPER('Homer');

Result:

+----------------+
| UPPER('Homer') |
+----------------+
| HOMER          |
+----------------+

Database Example

Here’s an example of selecting data from a database and converting it to uppercase:

USE Music;
SELECT 
    ArtistName AS Original, 
    UPPER(ArtistName) AS Uppercase
FROM Artists
LIMIT 5;

Result:

+------------------+------------------+
| Original         | Uppercase        |
+------------------+------------------+
| Iron Maiden      | IRON MAIDEN      |
| AC/DC            | AC/DC            |
| Allan Holdsworth | ALLAN HOLDSWORTH |
| Buddy Rich       | BUDDY RICH       |
| Devin Townsend   | DEVIN TOWNSEND   |
+------------------+------------------+

The LOWER() and LCASE() functions work the same way to convert characters to lowercase.

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.

Related posts:

• Finding the location of a string in a string with MySQL
• Rounding numbers with MySQL
• String concatenation with MySQL
• Maximum length for MySQL TEXT field types
• Renaming a table in MySQL
• Find the length of the longest string in MySQL