MySQL LOWER() and LCASE() – How to convert text to lowercase in MySQL?

By /
LOWER() And LCASE() Functions

In this tutorial, we will study the MySQL LOWER() and LCASE() functions. LOWER() and LCASE() are synonyms of each other in MySQL. They have a different name but do the same thing. The LOWER() and LCASE() functions are string functions that are used to convert a string to lower-case alphabets. For instance, if you have the following string – “HELLO”, on passing it as an argument in the LOWER() or LCASE() function, “hello” will be returned. It is important to note that if there are any lowercase letters in the string passed as an argument to LCASE() or LOWER() then they remain unaffected since they are already in lowercase.

Syntax for MySQL LOWER() and LCASE()

LOWER(string); 
LCASE(string);Code language: SQL (Structured Query Language) (sql)

Where string is an argument which needs to be converted to lower-case.

Examples of MySQL LOWER() and LCASE()

Let’s take a look at some of the examples of the MySQL LOWER function here.

1. Basic Examples

Let us convert the following to lowercase – “JournalDev”, “10, Downing Street”, “ANGRY BIRDS” and NULL. The queries we will use are:

SELECT LOWER(“JournalDev”); 
SELECT LOWER(“10, Downing Street”); 
SELECT LCASE(“ANGRY BIRDS”); 
SELECT LCASE(NULL);Code language: SQL (Structured Query Language) (sql)

And we get our output as:

MySQL Lower Lcase Basic Example

Note that passing NULL in LCASE() or LOWER() returns NULL.

2. MySQL LOWER()/ LCASE() on Table Data

Let us see how we can use the LOWER()/LCASE() functions on table data. Consider the below Employee table.

Employee Table Reverse MySQL LOWER and LCASE
Employee Table

Example 1

How about displaying the Name column and then their corresponding lowercase values. We will make use of an alias to make our result more readable.

SELECT Name, LOWER(Name) AS Name_In_LowerCase FROM Employee;Code language: SQL (Structured Query Language) (sql)

And we get our output as,

Lower Lcase Table Example1

Example 2

Now let us look at another example. As you can see the Office_Code column is full of uppercase values. We can also see that the values repeat. So let us convert the values in the Office_Code column to lowercase and then display the distinct values from them. We will make use of the DISTINCT keyword. The query for it is,

SELECT DISTINCT LCASE(Office_Code) AS Office_Code_In_Lowercase FROM Employee;Code language: SQL (Structured Query Language) (sql)

And we get our output as,

Lower Lcase Table Example2

3. Limitation of LOWER()/LCASE()

The LOWER() and LCASE() functions have a certain limitation. Let us take an example.

SET @str = BINARY 'Navi Mumbai'; 
SELECT LOWER(@str);Code language: SQL (Structured Query Language) (sql)

So we create a user-defined variable @str and we assign it a binary string containing the value ‘Navi Mumbai’. And then we use the LOWER() function and pass the variable as the argument. Our output is,

Limitation Of Lower

Wait, why did not convert to lowercase? LOWER() and LCASE() are ineffective when applied to binary strings. Binary strings have data types like BINARY, VARBINARY and BLOB. Since the above variable has a string with the BINARY data type, our LOWER() function is ineffective.

But I still want that string to be displayed in lowercase. So, what can be done? We convert the variable to an ordinary string (UTF-8 encoding) using the CONVERT() function. So our query is,

SELECT LOWER(CONVERT(@str USING utf8));Code language: SQL (Structured Query Language) (sql)

And our output now will be the string in lowercase as shown below.

Overcoming Limitation Of Lower

Conclusion

LOWER() and LCASE() can be useful while presenting your data or for string operations on your table data. I would encourage you to play around with these functions to understand how you can use them effectively.

References