MySQL UPPER() and UCASE() – How to convert text to uppercase in MySQL?

By /
UPPER() And UCASE() Functions

In this tutorial, we will study the MySQL UPPER() and UCASE() functions. Just like LOWER() and LCASE(), UPPER() and UCASE() are synonyms of each other in MySQL.

The UPPER() and UCASE() functions are string functions that are used to convert a string to uppercase alphabets. It is important to note that if there are any uppercase letters in the string passed as an argument to UCASE() or UPPER() then they remain unaffected since they are already in uppercase.

For instance, if you have the following string – “mysql”, on passing it as an argument in the UPPER() or UCASE() function, “MYSQL” will be returned. 

Syntax for MySQL UPPER() and UCASE()

UPPER(string); 
UCASE(string);Code language: SQL (Structured Query Language) (sql)

Where string is an argument which needs to be converted to uppercase.

Examples of MySQL UPPER() and UCASE()

Let’s take a look at some of the examples of the MySQL UPPER() and UCASE() functions here.

1. Basic Examples

Let us convert the following to uppercase – “JournalDev”, “india”, “PoKeMoN” and NULL. The queries we will use are –

SELECT UPPER(“JournalDev”); 
SELECT UPPER(“india”); 
SELECT UCASE(“PoKeMoN”); 
SELECT UCASE(NULL);Code language: SQL (Structured Query Language) (sql)

And we get our output as,

MySQL UPPER Ucase Basic Example

Note that passing NULL in UCASE() or UPPER() returns NULL.

2. MySQL UPPER()/UCASE() on Table Data

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

Employee Table Reverse MySQL UPPER and UCASE functions
Employee Table

Example 1

How about displaying the Department column and then their corresponding uppercase values. We will make use of a MySQL alias to make our result more readable. 

SELECT Department, UPPER(Department) AS Department_In_UpperCase FROM Employee;Code language: SQL (Structured Query Language) (sql)

And we get our output as,

Upper Ucase Table Data1

Example 2

Now let us look at another example. How about converting those names to uppercase that begin with the alphabet ‘V’. We will use the LIKE Operator. The query for it is:

SELECT Name, UCASE(Name) AS Name_In_Uppercase FROM Employee WHERE Name LIKE 'V%';Code language: SQL (Structured Query Language) (sql)

And we get our output as,

Upper Ucase Table Data2

3. Limitation of UPPER()/UCASE()

The UPPER() and UCASE() functions have a certain limitation. Let us take an example.

SET @str = BINARY 'geography’'; 
SELECT UPPER(@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 ‘geography’. And then we use the UPPER() function and pass the variable as the argument. Our output is,

Limitation Of Upper

Wait, why did not convert to uppercase? Just like the LOWER()/LCASE() functions, UPPER() and UCASE() are ineffective when applied to binary strings. Since the above variable has a string with the BINARY data type, our UPPER() function is ineffective.

To display the string in uppercase, we will use the following query using the CONVERT() function.

SELECT UPPER(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 Upper

Conclusion

UPPER() and UCASE() 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