SQL UPPER()

The SQL UPPER() function converts all characters in a specified string to uppercase.

This function is useful for standardizing text data, such as names or codes, for case-insensitive comparison or consistent display.

The UPPER() function is supported across major SQL databases, including SQL Server, MySQL, PostgreSQL, and Oracle.

In this tutorial, we will go through SQL UPPER() String function, its syntax, and how to use this function in SQL statements for string operations, with the help of well detailed examples.

Syntax of SQL UPPER() Function

The basic syntax of the SQL UPPER() function is:

</>
Copy
UPPER(string);

Each part of this syntax has a specific purpose:

  • string: The text that you want to convert to uppercase. This can be a column, variable, or literal string.

The UPPER() function returns the input string with all characters converted to uppercase.

Setup for Examples: Creating the Database and Table

We’ll create a sample customers table with fields first_name, last_name, and country. Follow these steps using MySQL or any SQL environment to set up the data for the UPPER() function examples.

1. First, create a new database called company:

</>
Copy
CREATE DATABASE company;

2. Select the company database to work with:

</>
Copy
USE company;

3. Create a table named customers with the fields customer_id, first_name, last_name, and country:

</>
Copy
CREATE TABLE customers (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    country VARCHAR(50)
);

4. Insert sample data into the customers table to use with the UPPER() function examples:

</>
Copy
INSERT INTO customers (first_name, last_name, country)
VALUES
('Alice', 'Johnson', 'usa'),
('Bob', 'Smith', 'Canada'),
('Charlie', 'Brown', 'UsA'),
('David', 'Lee', 'Australia'),
('Eve', 'Martinez', 'usa');
SQL UPPER() - Setup for examples

With this setup complete, we can run the UPPER() function examples to test and view results in the customers table.

Examples: Using UPPER() in SQL Queries

We’ll go through examples demonstrating the UPPER() function in SQL, using sample data from a customers table with fields first_name, last_name, and country.

customers table data for examples

1. Converting First Names to Uppercase

To display the first_name column in uppercase:

</>
Copy
SELECT first_name, 
       UPPER(first_name) AS uppercase_first_name
FROM customers;

This query converts each first_name value to uppercase for a standardized display.

SQL UPPER() - Example - Converting First Names to Uppercase

2. Creating Full Names in Uppercase

To concatenate first_name and last_name in uppercase to create a standardized full name:

</>
Copy
SELECT first_name, last_name, 
       CONCAT(UPPER(first_name), ' ', UPPER(last_name)) AS full_name_upper
FROM customers;

This query combines the uppercase first_name and last_name into a fully uppercase full_name.

Reference: SQL CONCAT()

SQL UPPER() - Example - Creating Full Names in Uppercase

3. Case-Insensitive Search Using UPPER()

To perform a case-insensitive search for customers from “USA” in the country column:

</>
Copy
SELECT first_name, last_name, country
FROM customers
WHERE UPPER(country) = 'USA';

This query converts country to uppercase, ensuring that all variations of “usa” (e.g., “Usa”, “uSa”) are matched.

Reference: SQL WHERE

SQL UPPER() - Example - Case-Insensitive Search Using UPPER()

4. Using UPPER() for Standardized Output in Reports

To ensure that all country names are displayed in uppercase for reporting:

</>
Copy
SELECT customer_id, first_name, last_name, 
       UPPER(country) AS country_uppercase
FROM customers;

This query converts country to uppercase to standardize the output for all records in the report.

SQL UPPER() - Example - Using UPPER() for Standardized Output

FAQs for SQL UPPER()

1. What does the SQL UPPER() function do?

The UPPER() function converts all characters in a specified string to uppercase.

2. Can UPPER() handle NULL values?

If the input string is NULL, UPPER() returns NULL.

3. Is UPPER() supported in all SQL databases?

Yes, UPPER() is supported in SQL Server, MySQL, PostgreSQL, Oracle, and other SQL databases.

4. How is UPPER() different from LOWER()?

UPPER() converts text to uppercase, while LOWER() converts text to lowercase.

5. Can UPPER() be used with other string functions?

Yes, UPPER() can be combined with functions like CONCAT(), TRIM(), and SUBSTRING() for advanced string manipulation.