SQL UPPER() Function
The SQL UPPER() function is a string function that converts all the strings in the table to uppercase.
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);
SQL UPPER() Function Examples
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.
Demo SQL Database
We will be using the following table in the examples:
ID | NAME |
|---|---|
12@tEsla | Elon |
x | Musk |
Microsoft | Bill |
To create this table in your system, write the following queries:
CREATE TABLE GFG_UPPER_DEMO(
Id Varchar(20),
Name varchar(20)
);
INSERT INTO GFG_UPPER_DEMO VALUES
("12@tEsla", "Elon"),
("x" ,"Musk"),
("Microsoft", "Bill") ;
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:
Converting Character Literal to Capital Case Using UPPER() function.
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:
Converting Character Literal to Capital Case Using UPPER() function.
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:
Converting a String with Numerical characters and Special Characters into Capital Case using UPPER() function.
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:
Passing a column name to the UPPER() function and Converting it to Capital Case.
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 the UCASE() function.
- Special Characters like “@”, “%”, “+” etc. and numerical characters remain unchanged, only the lowercase alphabetical letters get transformed into uppercase.