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)

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;

Here is the result:

'STRING FUNCTIONS'

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

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

The following picture illustrates the result:

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;

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

Here is the result:

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