Oracle UPPER

The Oracle UPPER() function converts all letters in a string to uppercase.

Syntax

The following illustrates the syntax of the Oracle UPPER() function:

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

Arguments

The UPPER() function takes one argument:

1) string

is the string which is converted to uppercase

Return value

The UPPER() function returns a string with all letters in uppercase.

Examples

The following statement converts  the string 'string function' to uppercase:

SELECT UPPER( 'string functions' ) FROM dual;
Code language: SQL (Structured Query Language) (sql)

Here is the result:

'STRING FUNCTIONS'
Code language: SQL (Structured Query Language) (sql)

Let’s see the contacts table in the sample database:

contacts table

The following statement groups contacts by their initials and returns the number of contacts for each alphabet.

SELECT UPPER( SUBSTR( first_name, 1, 1 ) ) initials, COUNT( * ) FROM contacts GROUP BY UPPER( SUBSTR( first_name, 1, 1 ) ) ORDER BY initials
Code language: SQL (Structured Query Language) (sql)

The following picture illustrates the result:

Oracle UPPER function example

You can use the UPPER() function to perform case insensitive search for values in a column. To demonstrate this, let’s update the last name of the contact id 38 from Hill to HILL:

UPDATE contacts SET last_name = 'HILL' WHERE contact_id = 38;
Code language: SQL (Structured Query Language) (sql)

To find contacts whose last name is Hill, HILL, or hill, you use the UPPER() function in the WHERE clause as follows:

SELECT contact_id, first_name, last_name, email FROM contacts WHERE UPPER( last_name ) = 'HILL';
Code language: SQL (Structured Query Language) (sql)

Here is the result:

Oracle UPPER - case insensitive search

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

Was this tutorial helpful?