Home » SQL String Functions » SQL UPPER Function

SQL UPPER Function

Savigo
You organize your data. Now organize your savings.
Set savings goals, track your progress, and stay on course.
Get Savigo for iPhone 

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)

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;

Try it

Output:

  result
-----------
 SQL UPPER

The following query uses the UPPER function with NULL:

SELECT
  UPPER(NULL) result;

Try it

Output:

 result
--------
 NULL

Transforming data to uppercase #

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

SQL UPPER Function: employees table

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;

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';

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 helpful?