SQL UPPER: Convert a String into Uppercase

Summary: this tutorial shows you how to use the SQL UPPER function to convert a string into uppercase.

Introduction to the SQL UPPER function

The SQL UPPER function converts all the letters in a string into uppercase. If you want to convert a string to lowercase, you use the LOWER function instead.

The syntax of the UPPER function is as simple as below.

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

If the input string is NULL, the UPPER function returns NULL, otherwise, it returns a new string with all letters converted to uppercase.

Besides the UPPER function, some database systems provide you with an additional function named  UCASE which is the same as the UPPER function. It is “there is more than one way to do it”.

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

SQL UPPER function examples

The following statement converts the string  sql upper to SQL UPPER:

SELECT UPPER('sql upper');Code language: SQL (Structured Query Language) (sql)

        UPPER
----------------------
SQL UPPER
(1 row)Code language: SQL (Structured Query Language) (sql)

Let’s take a look at the employees table in the sample database.

The following query uses the UPPER function to convert last names of employees to uppercase.

SELECT 
    UPPER(last_name)
FROM
    employees
ORDER BY UPPER(last_name);Code language: SQL (Structured Query Language) (sql)

The query just read the data from the employees table and convert them on the fly. The data in the table remains intact.

To convert data to uppercase in the database table, you use the UPDATE statement. For example, the following statement updates the emails of employees to uppercase.

UPDATE employees 
SET 
    email = UPPER(email);Code language: SQL (Structured Query Language) (sql)

Querying data case insensitive using the UPPER function

When you query the data using the WHERE clause, the database systems often match data case sensitively. For example, the literal string Bruce is different from bruce.

The following query returns no result.

SELECT 
    employee_id, 
    first_name
FROM
    employees
WHERE
    first_name = 'BRUCE';Code language: SQL (Structured Query Language) (sql)

To match data case insensitively, you use the UPER function. For example, the following query will return a row:

SELECT 
    employee_id, 
    first_name
FROM
    employees
WHERE
    UPPER(first_name) = 'BRUCE';Code language: SQL (Structured Query Language) (sql)

Notice that the query above scans the whole table to find the matching rơ. In case the table is big, the query will be very slow.

To overcome this, some database systems provide the function-based index that allows you to define an index based on a function of one or more columns that results in a better query performance.

In this tutorial, you have learned how to use the SQL UPPER function to convert a string to uppercase.