Back to: MySQL Tutorials for Beginners and Professionals
MySQL UCASE and LCASE Function with Examples
In this article, I am going to discuss MySQL UCASE and LCASE Function with Examples. Please read our previous article where we discussed MySQL AVG Function with Examples.
Understanding UCASE and LCASE Function in MySQL:
We are going to use the following Employee table to understand the MySQL UCASE (Upper Case) and LCASE (Lower Case) function with Examples.
Please use the following SQL Script to create and populate the Employee table with the required sample data.
CREATE DATABASE Company; USE Company; CREATE TABLE Employee ( Id INT PRIMARY KEY, Name VARCHAR(45) NOT NULL, Department VARCHAR(45) NOT NULL, Salary FLOAT NOT NULL, Gender VARCHAR(45) NOT NULL, Age INT NOT NULL, City VARCHAR(45) NOT NULL ); INSERT INTO Employee VALUES (1001, 'John Doe', 'IT', 35000, 'Male', 25, 'London'); INSERT INTO Employee VALUES (1002, 'Mary Smith', 'HR', 45000, 'Female', 27, 'London'); INSERT INTO Employee VALUES (1003, 'James Brown', 'Finance', 50000, 'Male', 28, 'London'); INSERT INTO Employee VALUES (1004, 'Mike Walker', 'Finance', 50000, 'Male', 28, 'London'); INSERT INTO Employee VALUES (1005, 'Linda Jones', 'HR', 75000, 'Female', 26, 'London'); INSERT INTO Employee VALUES (1006, 'Anurag Mohanty', 'IT', 35000, 'Male', 25, 'Mumbai'); INSERT INTO Employee VALUES (1007, 'Priyanla Dewangan', 'HR', 45000, 'Female', 27, 'Mumbai'); INSERT INTO Employee VALUES (1008, 'Sambit Mohanty', 'IT', 50000, 'Male', 28, 'Mumbai'); INSERT INTO Employee VALUES (1009, 'Pranaya Kumar', 'IT', 50000, 'Male', 28, 'Mumbai'); INSERT INTO Employee VALUES (1010, 'Hina Sharma', 'HR', 75000, 'Female', 26, 'Mumbai');
MySQL UCASE() Function
The MySQL UCASE function converts string data values to uppercase. The UCASE function only works on string columns. Following is the syntax to use the UCASE function in MySQL.
SELECT UCASE(columnname) FROM tablename;
SELECT UCASE(‘String Value’);
We need to pass the string column name to the UCASE function whose values we want in the Upper case. The changes will only affect the result set, it will not affect the original data present in the database table.
Example: Converting Employee Name and City to Upper Case
Let us see an example that will convert the employee’s name and city to upper case. Following is the SQL query which will convert the Name and City of Employees to upper case using the UCASE function.
SELECT Id, UCASE(Name) as Name, Department, Salary, UCASE(City) AS CITY FROM Employee;
Once you execute the above query, you will get the following output. Notice that the Name and City column data are in upper case.
UPPER Function in MySQL:
The UCASE function is the synonym for the UPPER function. That means both the functions are going to do the same task i.e. going to convert the given string into upper case. The following example uses the UPPER function and will also give the same output as the UCASE function.
SELECT Id, UPPER(Name) as Name, Department, Salary, UPPER(City) AS CITY FROM Employee;
When you execute the above query, you will get the following output. Notice that the Name and City column data are in upper case.
MySQL LCASE() Function
The MySQL LCASE function converts the string data row values to lowercase. The LCASE function only works on string columns. Following is the syntax to use the UCASE function in MySQL.
SELECT LCASE(columnname) FROM tablename;
SELECT LCASE(‘String Value’);
We need to pass the string column name to the LCASE function whose values we want in lower case. The changes will only affect the result set, it will not affect the original data present in the database table.
Example: Converting Employee Name and City to Lower Case
Let us see an example that will convert the employee’s name and city to lower case. Following is the SQL query which will convert the Name and City of Employees to lower case using the LCASE function.
SELECT Id, LCASE(Name) as Name, Department, Salary, LCASE(City) AS CITY FROM Employee;
Once you execute the above query, you will get the following output. Notice that the Name and City column data are in lower case.
LOWER Function in MySQL:
The LCASE function is the synonym for the LOWER function. That means both the functions are going to do the same task i.e. going to convert the given string into lower case. The following example uses the LOWER function and will also give the same output as the LCASE function.
SELECT Id, LOWER(Name) as Name, Department, Salary, LOWER(City) AS CITY FROM Employee;
When you execute the above query, you will get the following output. Notice that the Name and City column data are in lower case.
In the next article, I am going to discuss MySQL MID Function with Examples. Here, in this article, I try to explain the MySQL UCASE and LCASE Functions with Examples. I hope this MySQL UCASE and LCASE Functions article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.