How to Convert Strings to Uppercase in MySQL

Convert Strings to Uppercase in MySQL

MySQL provides the UPPER() function to transform any lowercase or mixed-case strings into uppercase.

In MySQL, converting strings to uppercase is useful for data normalization, case-insensitive comparisons, or standardizing outputs in reports.

---

Using the UPPER() Function in MySQL

The UPPER() function in MySQL takes a string as input and returns the entire string in uppercase. It can be applied to both literal strings and table column values. The syntax for UPPER() is straightforward:

UPPER(string)

Here, string can be any text or column that you want to convert to uppercase. Let’s set up an example table and explore different ways to use UPPER() to achieve uppercase text transformations.

---

Setting Up Example Table

For this tutorial, we’ll create a users table with sample data, including fields for username, email, and city. These fields will help demonstrate different ways to use UPPER() for uppercase transformations.

1. First, create a new database called app_db:

CREATE DATABASE app_db;

2. Use the app_db database:

USE app_db;

3. Create a users table with columns for username, email, and city:

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50),
    email VARCHAR(100),
    city VARCHAR(50)
);

4. Insert sample data into the users table:

INSERT INTO users (username, email, city)
VALUES ('alice123', 'alice@example.com', 'new york'),
       ('bob.smith', 'bob.smith@example.com', 'los angeles'),
       ('charlie89', 'charlie@example.com', 'chicago');

Now that we have sample data in place, let’s proceed with examples of using UPPER() to convert strings to uppercase in various scenarios.

---

Examples: Using UPPER() in MySQL Queries

We’ll explore how to use the UPPER() function in SQL queries to convert text to uppercase in MySQL.

---

1. Converting Usernames to Uppercase

To display all username values in uppercase format:

SELECT username, UPPER(username) AS uppercase_username
FROM users;

This query returns each username in uppercase, allowing for standardized display of user handles.

---

2. Converting City Names to Uppercase

To display each user’s city in uppercase:

SELECT city, UPPER(city) AS uppercase_city
FROM users;

This query shows each city in uppercase, useful for reports or comparisons that require consistent formatting.

---

3. Using UPPER() for Case-Insensitive Searches

To search for users from “NEW YORK” regardless of the case stored in the database:

SELECT *
FROM users
WHERE UPPER(city) = 'NEW YORK';

This query finds all users where the city value, regardless of case, matches “NEW YORK”. Using UPPER() on both sides makes the search case-insensitive.

---

4. Combining UPPER() with CONCAT() for Custom Output

To display each user’s email in uppercase and formatted with a prefix, for example, “Email: EMAIL@EXAMPLE.COM”:

SELECT CONCAT('Email: ', UPPER(email)) AS formatted_email
FROM users;

This query returns each user’s email in uppercase with the prefix “Email:”, ideal for generating formatted outputs for reporting or user interfaces.