How to Convert Uppercase Characters to Lowercase in MySQL

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

The syntax goes like this:

LOWER(str)

Or…

LCASE(str)

Where str is the string you want converted to lowercase.

Example

Here’s an example:

SELECT LOWER('ATTENTION');

Result:

+--------------------+
| LOWER('ATTENTION') |
+--------------------+
| attention          |
+--------------------+

Of course, if the string already contains any lowercase characters, then they will remain lowercase.

Example:

SELECT LOWER('Attention');

Result:

+--------------------+
| LOWER('ATTENTION') |
+--------------------+
| attention          |
+--------------------+

Database Example

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

USE Music;
SELECT 
    ArtistName AS Original, 
    LOWER(ArtistName) AS Lowercase
FROM Artists
LIMIT 5;

Result:

+------------------+------------------+
| Original         | Lowercase        |
+------------------+------------------+
| Iron Maiden      | iron maiden      |
| AC/DC            | ac/dc            |
| Allan Holdsworth | allan holdsworth |
| Buddy Rich       | buddy rich       |
| Devin Townsend   | devin townsend   |
+------------------+------------------+

The UPPER() and UCASE() functions work the same way to convert characters to uppercase.

How the LOWER() Function Works in MySQL

In MySQL, the LOWER() function converts uppercase characters to lowercase, according to the current character set mapping (the default mapping is utf8mb4).

Syntax

The syntax goes like this:

LOWER(str)

Where str is the string to be changed to lowercase.

Example

Here’s an example:

SELECT LOWER('CAT');

Result:

+--------------+
| LOWER('CAT') |
+--------------+
| cat          |
+--------------+

Of course, if the string already contains any lowercase characters, those characters will remain lowercase.

Example:

SELECT LOWER('Cat');

Result:

+--------------+
| LOWER('Cat') |
+--------------+
| cat          |
+--------------+

Database Example

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

USE Music;
SELECT 
    ArtistName AS Original, 
    LOWER(ArtistName) AS Lowercase
FROM Artists
LIMIT 5;

Result:

+------------------+------------------+
| Original         | Lowercase        |
+------------------+------------------+
| Iron Maiden      | iron maiden      |
| AC/DC            | ac/dc            |
| Allan Holdsworth | allan holdsworth |
| Buddy Rich       | buddy rich       |
| Devin Townsend   | devin townsend   |
+------------------+------------------+

Binary Strings

This function doesn’t work on binary strings. If you need to use it on a binary string, you’ll need to convert it to a nonbinary string first. Here’s an example:

SET @str = BINARY 'Cat';
SELECT 
  LOWER(@str) AS 'Binary', 
  LOWER(CONVERT(@str USING utf8mb4)) AS 'Nonbinary';

Result:

+--------+-----------+
| Binary | Nonbinary |
+--------+-----------+
| Cat    | cat       |
+--------+-----------+

The LCASE() Function

The LCASE() function is a synonym for LOWER(). Note that if you use LCASE() within a view, it will be rewritten and stored as LOWER().

Convert to Uppercase

The UPPER() and UCASE() functions work the same way to convert characters to uppercase.

How the LCASE() Function Works in MySQL

In MySQL, the LCASE() function is a synonym for LOWER(). In other words, you can use either of these functions to do the same thing.

Specifically, both functions convert uppercase characters to lowercase, according to the current character set mapping (the default mapping is utf8mb4).

Syntax

The syntax goes like this:

LCASE(str)

Where str is the string to be changed to lowercase.

Example

Here’s an example:

SELECT 
  LCASE('CAT'),
  LOWER('CAT');

Result:

+--------------+--------------+
| LCASE('CAT') | LOWER('CAT') |
+--------------+--------------+
| cat          | cat          |
+--------------+--------------+

Binary Strings

These functions don’t work on binary strings. If you need to use them on a binary string, you’ll need to convert the string to a nonbinary string first. Here’s an example:

SET @str = BINARY 'Cat';
SELECT 
  LCASE(@str) AS 'Binary', 
  LCASE(CONVERT(@str USING utf8mb4)) AS 'Nonbinary';

Result:

+--------+-----------+
| Binary | Nonbinary |
+--------+-----------+
| Cat    | cat       |
+--------+-----------+

Using LCASE() in Views

One thing to be aware of is that, when the LCASE() function is used within a view, it is rewritten and stored as LOWER().