SQL UPPER() Function

In SQL, the UPPER() function is one of the most commonly used string functions. It is used to convert all characters in a given string to uppercase. Whether we are dealing with textual data that requires uniform formatting or need to compare strings without case sensitivity, the UPPER() function comes in handy.

In this article, we will cover the SQL UPPER() function, including its syntax, examples, and best practices for use.

UPPER() Function in SQL

The UPPER function in SQL is an in-built function that changes lowercase characters or strings to capital cases. Certain characters like integers (0-9) or special characters like ("@", "-" "/", "&" etc.) remain unchanged in the result.

It is used to display the output in CAPITAL CASE and is supported by all major SQL-based DBMSs.

Note: UPPER and UCASE functions perform the same operation on strings. In older versions of SQL, some databases (like Db2 DBMS)used the UCASE function, while others used the UPPER function. Now both the functions are supported by all major databases.

The UPPER function is supported in SQL Server (starting with 2008), Azure SQL Database, Azure SQL Data Warehouse, Parallel Data Warehouse, MySQL 4.0, Oracle, DB2, and all other major Database systems.

Syntax:

SQL UPPER function syntax is:

UPPER(input_text);

OR

UPPER(column_name);

Examples of SQL UPPER() Function

Let's look at the examples of UPPER() function in SQL. Check the SQL UPPER function with examples to understand it better.

First, let's create a demo database and table on which we will use the UPPER function.

We will be using the following table in the examples:

Example 1: Convert Character Literal to Uppercase Using SQL Upper Function

In this example, we are printing the x value as it is in first column, and in the second column we are printing the ID value after applying the UPPER function, and the UPPER function converted "x" into "X".

Query:

SELECT  "x" as  "BEFORE UPPER() Function" ,  UPPER("x")  as  "AFTER UPPER() Function";

Output:

Example 2: Convert string to Uppercase using SQL UPPER Function

IIn this example, in the first column, we are printing the "Microsoft" word as it is, and in the second column we are printing the "Microsoft" value after applying the UPPER() function, and the UPPER function converted "Microsoft" into "MICROSOFT".

Query:

SELECT  "Microsoft"  as  "BEFORE UPPER() Function" ,  UPPER("Microsoft")  as  "AFTER UPPER() Function";

Output:

Example 3: Using UPPER Function on String Consisting of Special Characters, Integers, and Alphabets

In this example, in the first column, we are printing the ID value as it is, and in the second column we are printing the ID value after applying the UPPER function, and "12@tEsla" is converted to "12@TESLA". The special characters and Numerical characters remained the same, only the alphabets are converted into capital case.

SELECT  "12@tEsla"  as  "BEFORE UPPER() Function" ,  UPPER("12@tEsla")  as  "AFTER UPPER() Function" ;

Output:

Example 4: Using SQL UPPER function on a Column

In this example, we passed a column name ID as an argument to the UPPER() function. It converts all the values present in the ID column and outputs. In the first column, we can see the ID values before using the UPPER() function. In the second column, We can see all the alphabetic characters get converted into Capital Cases. Integer and special characters remain as it is. As we can see, "x", "Microsoft", and "12@tEsla" are converted into "X", "MICROSOFT", and "12@TESLA" respectively.

Query:

SELECT  ID  as  "BEFORE UPPER() Function" ,  UPPER(ID)  as  "AFTER UPPER() Function"  FROM GFG_UPPER_DEMO;

Output:

Important Points About SQL UPPER() Function

• UPPER()function in SQL allows us to convert the string text into uppercase.
• It takes only one parameter and converts the entire string into upper case.
• It is similar to theUCASE() function.
• Special Characters like "@", "%", "+" etc. and numerical characters remain unchanged, only the lowercase alphabetical letters get transformed into uppercase.

Conclusion

The SQL UPPER() function is a versatile tool for converting string data into uppercase. It is primarily used to ensure uniformity in data presentation and to perform case-insensitive comparisons. By using UPPER() in your queries, we can clean up inconsistent case usage in our database, standardize outputs, and simplify comparisons.

From simple string manipulations to case-insensitive searches, the UPPER() function provides an efficient way to handle text data in SQL. By following best practices and understanding how to use this function effectively, you can make your database operations more consistent and reliable.