Db2 UPPER

Summary: in this tutorial, you will learn how to use the Db2 UPPER() function to convert all characters of a string to uppercase.

DB2 UPPER() function overview

The UPPER() function accepts a string and returns a new string in which all characters in the original string are converted to uppercase.

Here is the syntax of the UPPER() function

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

In this syntax, the expression must evaluate to a character string or a value that can be implicitly converted to a character string.

The UPPER() function is useful for case-insensitive searches.

Db2 UPPER() function examples

Let’s take some examples of using the UPPER() function.

1) Using Db2 UPPER() to convert a literal string to uppercase.

This example uses the UPPER() function to convert the string 'Db2 upper' to uppercase:

SELECT
    UPPER('Db2 upper') result
FROM
    sysibm.sysdummy1;
Code language: SQL (Structured Query Language) (sql)

Here is the result set:

RESULT    
--------- 
DB2 UPPER 
Code language: SQL (Structured Query Language) (sql)

2) Using Db2 UPPER() to perform case insensitive searches

See the following authors table from the sample database:

The following example uses the UPPER() function to search for authors whose last name are Anderson:

SELECT
    author_id,
    first_name,
    last_name
FROM
    authors
WHERE 
    UPPER(last_name) = 'ANDERSON';
Code language: SQL (Structured Query Language) (sql)

Here is the output:

To speed up this query, you should consider creating an expression-based index for the last_name column:

CREATE INDEX ix_ulastname
ON authors(UPPER(last_name));
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the Db2 UPPER() function to convert all characters of a string to uppercase.