How to create a lowercase string in MySQL

Let's learn about MySQL lowercase transform functions

Posted on November 08, 2021


MySQL allows you to transform a string of characters into an all lowercase string by using the LOWER() or LCASE() functions.

You need to provide the string you wish to transform into all lowercase as the argument to the functions:

LOWER(str)

-- OR

LCASE(str)

For example, the following query transforms LONDON as london:

SELECT LOWER("LONDON");

-- +-----------------+
-- | LOWER("LONDON") |
-- +-----------------+
-- | london          |
-- +-----------------+

The function can only transforms one string at a time, so passing multiple strings will result in an error:

SELECT LOWER("LONDON", "YORK");

-- ERROR 1582 (42000): Incorrect parameter count in the call to native function 'LOWER'

To transform multiple strings, you need to call the function for each string separated by a comma:

SELECT LOWER("LONDON"), LOWER("YORK");

-- +-----------------+---------------+
-- | LOWER("LONDON") | LOWER("york") |
-- +-----------------+---------------+
-- | london          | york          |
-- +-----------------+---------------+

Finally, you can also pass a column name as the argument to the LOWER or LCASE functions to run the function on each column value.

Suppose you have a table called cities with the following data:

+----+-----------+
| id | name      |
+----+-----------+
|  1 | LONDON    |
|  2 | YORK      |
|  3 | BRISTOL   |
|  4 | LIVERPOOL |
+----+-----------+

You can pass the name column into the LOWER function as shown below:

SELECT LOWER(name) FROM cities;

The returned result set will be as follows:

+-------------+
| LOWER(name) |
+-------------+
| london      |
| york        |
| bristol     |
| liverpool   |
+-------------+

And that’s how you transform a string of characters into its lowercase version in MySQL.

If you want to do the opposite and transform a string into its uppercase version, then you can use the MySQL UPPER function and its alias UCASE function.

Related articles:

Level up your programming skills

I'm sending out an occasional email with the latest programming tutorials. Drop your email in the box below and I'll send new stuff straight into your inbox!

No spam. Unsubscribe anytime.