In this tutorial, we will study the MySQL
LCASE() are synonyms of each other in MySQL. They have a different name but do the same thing. The
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
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
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:
Note that passing NULL in
LOWER() returns NULL.
2. MySQL LOWER()/ LCASE() on Table Data
Let us see how we can use the
LCASE() functions on table data. Consider the below Employee table.
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,
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,
3. Limitation of LOWER()/LCASE()
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,
Wait, why did not convert to lowercase?
LCASE() are ineffective when applied to binary strings. Binary strings have data types like
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.
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.
- MySQL Official Documentation on