MySQL LOWER() and LCASE() Functions

This tutorial explains MySQL LOWER()/LCASE() functions which convert the upper case characters of a string to the LOWER case. We’ll describe the functioning of this method with the help of simple examples.

1. LOWER()/LCASE() Syntax
2. LOWER()/LCASE() to convert text to lower case
3. LOWER()/LCASE() on table data
4. LOWER()/LCASE() on binary text

Let’s now go through each of the sections one by one.

MySQL LOWER()/LCASE() Functions

As stated initially, LOWER() is a built-in MySQL function that changes a string value to the LOWER case. So, let’s now see the details and check out how can we use it.

Syntax

Below is the signature of this method:

# MySQL Function to convert text to LOWER case
LOWER(Given_string);

Below is the description of the parameter used in the above function.

+--------------+-------------------------------------+
| # Params     | # Description                       |
+--------------+-------------------------------------+
| Given_string | Input string argument in upper case |
+-------------+--------------------------------------+

MySQL also provides LCASE() which is another function to convert a string to the LOWER case. It takes some text input and produces the result in capitalized format.

# Another MySQL function to capitalize a string
LCASE(Given_string);

Later, in examples, you’ll see that both LOWER() and LCASE() are producing a similar output.

MySQL UPPER()/UCASE() Functions

MySQL LOWER()/LCASE() Examples

Let’s now unveil several examples addressing different situations.

Using LOWER()/LCASE() to convert text to lower case

Both LOWER() and LCASE() functions can take a standard or alpha-numeric TEXT and turn to the LOWER case. See the below example.

SELECT LOWER('PYTHON PROGRAMMING');
SELECT LOWER('PYTHON VERSION 3.6');

Check the result/outcome of the LOWER() function below.

1 MySQL Workbench
python programming
python version 3.6

Next, we’ll run the same test using the MySQL LCASE() function.

SELECT LCASE('PYTHON PROGRAMMING');
SELECT LCASE('PYTHON VERSION 3.6');

You can see that LCASE() also produced the same as LOWER() did.

1 MySQL Workbench
python programming
python version 3.6

Calling LOWER()/LCASE() on table fields

In this example, we are applying the LOWER() on a table column. Here, we will print the company name in capital format.

-- Using MySQL LOWER on table data
SELECT 
    company_name, 
    LOWER(company_name) LOWER_CASE
FROM
    company_list
ORDER BY 
    company_name
LIMIT 5;

After executing the above command, the output will be:

1 MySQL Workbench
apple
bing
ca
dell
google

Let’s now use LCASE on the same table and see what it does.

-- Using MySQL LCASE on table data
SELECT 
    company_name, 
    LCASE(company_name) LCASE
FROM
    company_list
ORDER BY 
    company_name
LIMIT 5;

After executing the above command, the output is the same:

1 MySQL Workbench
apple
bing
ca
dell
google

Also Read: MySQL CONCAT to Concatenate Strings

MySQL LOWER()/LCASE() on binary text

MySQL types such as BINARY, VARBINARY, or BLOB are binary data. The LOWER() function is not directly compatible with these.

Hence, we first have to convert them to be compatible with the LOWER() function. See the example below.

-- Convert binary text to string and then in LOWER case using LOWER()
SET @binary_data = BINARY 'BINARY SAMPLE TEXT';
SELECT LOWER(@binary_data), LOWER(CONVERT(@binary_data USING UTF8MB4)) LOWER_FUNC;

After execution, we get this:

1 MySQL Workbench
BINARY SAMPLE TEXT   binary sample text

Similarly, we’ll now use LCASE() instead of LOWER() for the above test case.

-- Convert binary text to string and then in LOWER case using LCASE()
SET @binary_data = BINARY 'BINARY SAMPLE TEXT';
SELECT LCASE(@binary_data), LCASE(CONVERT(@binary_data USING UTF8MB4)) LCASE_FUNC;

After execution, we get this:

1 MySQL Workbench
BINARY SAMPLE TEXT   binary sample text

We hope that after wrapping up this tutorial, you will feel comfortable using the MySQL LOWER() and LCASE() functions. However, you may practice more with examples to gain confidence.

Also, to learn SQL from scratch to depth, read our step-by-step MySQL tutorial.