Oracle UPPER Function: A Simple Guide

The UPPER function in Oracle SQL is a straightforward and widely used function that converts all letters in a string to uppercase.

It's an essential tool for standardizing data, formatting reports, and performing case-insensitive searches.

What is the UPPER Function in Oracle?

The UPPER function takes a string (or a column) and returns a new string where every letter has been converted to its uppercase equivalent. Any non-alphabetic characters (like numbers, spaces, or symbols) are left unchanged.

This is useful for:

• Data Standardization: Ensuring all data in a column (like country codes or status flags) follows the same uppercase format (e.g., 'usa' becomes 'USA').
• Case-Insensitive Searching: Using UPPER on both sides of a WHERE clause allows you to find matches regardless of the original case.
• Formatting Output: Making headers or titles in a report look consistent.

UPPER Function Syntax

The syntax for UPPER is very simple:

UPPER(char)

Let's break that down:

• char: The original string or column you want to convert to uppercase (e.g., 'Hello World').

Oracle UPPER Function Examples

Here are two practical examples of how to use UPPER.

Example 1: Converting a Simple String with UPPER

This example shows the most basic use of UPPER on a mixed-case string.

Query:

SELECT 
  UPPER('Hello, World! 123') AS "Uppercase String"
FROM DUAL;

Result:

Uppercase String
-----------------
HELLO, WORLD! 123

Notice that only the letters were changed. The comma, space, exclamation mark, and numbers were not affected.

Example 2: Using UPPER in a WHERE Clause (Case-Insensitive Search)

This is a very common technique. Imagine you want to find an employee named 'Smith', but you don't know if it was stored as 'Smith', 'smith', or 'SMITH'.

Query:

-- This will find all variations like 'Smith', 'smith', etc.
SELECT 
  first_name, 
  last_name
FROM employees
WHERE UPPER(last_name) = 'SMITH';

Result: (This query would return all employees whose last_name is 'SMITH', regardless of its original capitalization in the table.)