How to convert all the records in a MySQL table from uppercase to lowercase?

Convert all the records in a MySQL table from uppercase to lowercase using UPDATE command along with LOWER() method.

Let us first create a table −

mysql> create table DemoTable
   (
   Id varchar(100),
   StudentFirstName varchar(20),
   StudentLastName varchar(20),
   StudentCountryName varchar(10)
   );
Query OK, 0 rows affected (0.61 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable values('STU-101','John','Smith','US');
Query OK, 1 row affected (0.59 sec)
mysql> insert into DemoTable values('STU-102','John','Doe','UK');
Query OK, 1 row affected (0.15 sec)
mysql> insert into DemoTable values('STU-103','David','Miller','AUS');
Query OK, 1 row affected (0.19 sec)

Following is the query to display all records from the table using select statement −

mysql> select *from DemoTable;

This will produce the following output −

+---------+------------------+-----------------+--------------------+
| Id      | StudentFirstName | StudentLastName | StudentCountryName |
+---------+------------------+-----------------+--------------------+
| STU-101 | John             | Smith           | US                 |
| STU-102 | John             | Doe             | UK                 |
| STU-103 | David            | Miller          | AUS                |
+---------+------------------+-----------------+--------------------+
3 rows in set (0.00 sec)

Here is the query to change the case on every field in a MySQL table in a single call.

mysql> update DemoTable
set Id=lower(Id),
StudentFirstName=lower(StudentFirstName),
StudentLastName=lower(StudentLastName),
StudentCountryName=lower(StudentCountryName);
Query OK, 3 rows affected (0.22 sec)
Rows matched: 3 Changed: 3 Warnings: 0

Display all records from the table using select statement to check the changes done in the above query −

mysql> select *from DemoTable;

This will produce the following output −

+---------+------------------+-----------------+--------------------+
| Id      | StudentFirstName | StudentLastName | StudentCountryName |
+---------+------------------+-----------------+--------------------+
| stu-101 | john             | smith           | us                 |
| stu-102 | john             | doe             | uk                 |
| stu-103 | david            | miller          | aus                |
+---------+------------------+-----------------+--------------------+
3 rows in set (0.00 sec)