MySQL UPPER() Function

Introduction

In this chapter, we will learn about the UPPER() function in MySQL. The UPPER() function is used to convert all characters in a string to uppercase. This function is useful for standardizing text data, performing case-insensitive comparisons, and formatting text data. We will cover the syntax for the UPPER() function, a complete example of its usage, and important considerations for using it in MySQL.

Syntax

The basic syntax for using the UPPER() function in MySQL is:

SELECT UPPER(string);

• string: The string to be converted to uppercase.

Complete Example

Let’s go through a complete example where we create a database and table, insert data, and demonstrate the usage of the UPPER() function.

1. Create a Database and Table

CREATE DATABASE company;
USE company;

CREATE TABLE employees (
    employee_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100)
);

INSERT INTO employees (first_name, last_name, email) VALUES
('Rahul', 'Sharma', 'rahul.sharma@example.com'),
('Priya', 'Singh', 'priya.singh@example.com'),
('Amit', 'Kumar', 'amit.kumar@example.com');

2. Using UPPER() to Convert Strings to Uppercase

We will use the UPPER() function to convert the first_name and last_name columns to uppercase.

SELECT
    first_name,
    last_name,
    UPPER(first_name) AS upper_first_name,
    UPPER(last_name) AS upper_last_name
FROM employees;

Output:

3. Case-Insensitive Email Comparison

We will use the UPPER() function to perform a case-insensitive comparison of email addresses.

SELECT
    first_name,
    last_name,
    email
FROM employees
WHERE UPPER(email) = 'RAHUL.SHARMA@EXAMPLE.COM';

Output:

4. Combining UPPER() with Other String Functions

We can combine the UPPER() function with other string functions for more complex text manipulations.

Example: Uppercase Initials of First and Last Names

SELECT
    first_name,
    last_name,
    CONCAT(UPPER(SUBSTRING(first_name, 1, 1)), SUBSTRING(first_name, 2)) AS formatted_first_name,
    CONCAT(UPPER(SUBSTRING(last_name, 1, 1)), SUBSTRING(last_name, 2)) AS formatted_last_name
FROM employees;

Output:

Important Considerations

• Case Sensitivity: The UPPER() function is useful for standardizing text data to uppercase, which is particularly helpful in case-insensitive comparisons and searches.
• Character Sets: Ensure that the character set of the column being converted supports the characters in the string. The UPPER() function works with most character sets, but it’s always good to confirm compatibility.
• Performance: Using the UPPER() function on large datasets can impact performance. Optimize your queries by converting text to uppercase only when necessary and considering the impact on query execution time.

Conclusion

The UPPER() function in MySQL is used for converting all characters in a string to uppercase. This chapter covered the syntax for using the UPPER() function, provided a complete example of its usage, and discussed important considerations.