SQL UPPER Function

Summary: in this tutorial, you’ll learn how to use the SQL UPPER function to return a new string with all characters converted to uppercase.

Introduction to the SQL UPPER function #

The UPPER function accepts a string and returns a new string with all characters converted to uppercase.

Here’s the syntax of the UPPER function:

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

The UPPER function returns a new string with all characters of the string converted to uppercase. If the string is NULL, the UPPER function returns NULL.

Basic SQL UPPER function example #

The following statement uses the UPPER function to convert the string sql upper to uppercase:

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

Try it

Output:

  result
-----------
 SQL UPPERCode language: SQL (Structured Query Language) (sql)

The following query uses the UPPER function with NULL:

SELECT
  UPPER(NULL) result;Code language: PHP (php)

Try it

Output:

 result
--------
 NULLCode language: PHP (php)

Transforming data to uppercase #

We’ll use the employees table from the sample database.

The following query uses the UPPER function to retrieve data from the last_name column and convert it to uppercase:

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

Output:

 formatted_last_name
---------------------
 AUSTIN
 BAER
 BAIDA
 BELL
 CHEN
...

In this example, we retrieve data from the last_name column of the employees table and convert them to uppercase on the fly. The data in the last_name column of the employees remains intact.

Filtering data case-insensitively #

When filtering data using the WHERE clause, database systems match character strings case-sensitively. For example, the literal string Bruce is different from bruce.

To match text string case-insensitively, you use the UPPER function in the WHERE clause like this:

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

Try it

Notice that this query scans the whole table to find the matching row. In case the table is big, it will be slow.

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

Summary #

Use the UPPER function to return a new string with all characters converted to uppercase from a string.

Databases #

Was this tutorial helpful ?