UPPER Function in SQL

This string function shows all the string characters in the upper case in Structured Query Language. It converts the small character or set of small characters into capital letters.

We can also use the UPPER function with the string fields of the SQL table.

Syntax of UPPER String Function

Syntax1: This syntax uses the UPPER function with the column names of the SQL table:

SELECT UPPER(Column_Name) AS Alias_Name FROM Table_Name;  

In the syntax, we have to specify the column’s name on which we want to use the UPPER string function.

Syntax2: This syntax uses the UPPER function with the set of lower case characters (string):

SELECT UPPER(String);  

Syntax2: This syntax uses the UPPER function with the individual lower case character:

SELECT UPPER(lower_case_character);  

Examples of UPPER String function

Example 1: The following SELECT query converts all the characters of the following string in upper-case:

SELECT UPPER(javatpoint is a good website);  

Output:

JAVATPOINT IS A GOOD WEBSITE

Example 2: The following SELECT query cannot change the characters of the following string because the UPPER function cannot change the symbols and integers of the string in SQL.

SELECT UPPER(@#$12453@#);  

Output:

@#$12453@#

Example 3: The following SELECT query converts the small letters into capital letters:

SELECT UPPER(  New Delhi IS the Capital OF India);  

Output:

NEW DELHI IS THE CAPITAL OF INDIA

Example 4: The following SELECT query shows the character ‘s’ in upper-case in the output:

SELECT UPPER( s );  

Output:

S

Example 5: This example uses the UPPER function with the SQL table

In this example, we are going to create a new table, whose string columns will contains lower-case characters.

The syntax for creating the new table in the SQL database is as follows:

CREATE TABLE table_name  

(  

1st_Column Data Type (character_size of 1st Column),    

2nd_Column Data Type (character_size of the 2nd column ),    

3rd_Column Data Type (character_size of the 3rd column),    

...    

  

Nth_Column Data Type (character_size of the Nth column)  

);  

The following CREATE statement creates the Faculty_Info table:

CREATE TABLE Faculty_Info  

(  

Faculty_ID INT NOT NULL PRIMARY KEY,    

Faculty_First_Name VARCHAR (100),    

Faculty_Last_Name VARCHAR (100),    

Faculty_Dept_Id INT NOT NULL,  

Faculty_Address Varchar(120),  

Faculty_City Varchar (80),  

Faculty_Salary INT   

);

The below INSERT queries insert the records of college Faculties in the Faculty_Info table:

INSERT INTO Faculty_Info (Faculty_ID, Faculty_First_Name, Faculty_Last_Name Faculty_Dept_Id, Faculty_Address, Faculty_City, Faculty_Salary) VALUES (1001, arush, sharma, 4001, aman vihar, delhi, 20000);  

INSERT INTO Faculty_Info (Faculty_ID, Faculty_First_Name, Faculty_Last_Name Faculty_Dept_Id, Faculty_Address, Faculty_City, Faculty_Salary) VALUES (1002, bulbul, roy, 4002, nirman vihar, delhi, 38000 );  

INSERT INTO Faculty_Info (Faculty_ID, Faculty_First_Name, Faculty_Last_Name Faculty_Dept_Id, Faculty_Address, Faculty_City, Faculty_Salary) VALUES (1004, saurabh, sharma, 4001, sector 128, mumbai, 45000);  

INSERT INTO Faculty_Info (Faculty_ID, Faculty_First_Name, Faculty_Last_Name Faculty_Dept_Id, Faculty_Address, Faculty_City, Faculty_Salary) VALUES (1005, shivani, singhania, 4001, vivek vihar, kolkata, 42000);  

INSERT INTO Faculty_Info (Faculty_ID, Faculty_First_Name, Faculty_Last_Name Faculty_Dept_Id, Faculty_Address, Faculty_City, Faculty_Salary) VALUES (1006, avinash, sharma, 4002, sarvodya calony, delhi, 28000);  

INSERT INTO Faculty_Info (Faculty_ID, Faculty_First_Name, Faculty_Last_Name Faculty_Dept_Id, Faculty_Address, Faculty_City, Faculty_Salary)VALUES (1007, shyam, besas, 4003, krishna nagar, lucknow, 35000);  

The following SELECT statement displays the inserted records of the above Faculty_Info table:

SELECT * FROM Faculty_Info;  

Faculty_Id	Faculty_First_Name	Faculty_Last_Name	Faculty_Dept_Id	Faculty_Address	Faculty_City	Faculty_Salary
1001	arush	sharma	4001	aman vihar	delhi	20000
1002	bulbul	roy	4002	nirman vihar	delhi	38000
1004	saurabh	roy	4001	sector 128	mumbai	45000
1005	shivani	singhania	4001	vivek vihar kolkata 42000
1006	avinash	sharma	4002	sarvodya calony	delhi	28000
1007	shyam	besas	4003	krishna nagar	lucknow	35000

The following SELECT query uses the UPPER function with the Faculty_Last_Name column of the above Faculty_Info table:

SELECT Faculty_Last_Name, UPPER(Faculty_Last_Name) AS UPPER_LastName FROM Faculty_Info;  

This SQL statement converts the last name in upper case of each faculty of the above table.

Output:

Faculty_Last_Name	UPPER_LastName
sharma	SHARMA
sroy	ROY
sroy	ROY
ssinghania	SINGHANIA
ssharma	SHARMA
sbesas	BESAS

The following SELECT query uses the UPPER function with the Faculty_First_Name, Faculty_City, and Faculty_Address columns of those faculties whose faculty_Id is greater than 1002 in the above Faculty_Info table:

SELECT Faculty_Id, UPPER(Faculty_First_Name), UPPER(Faculty_Address), UPPER(Faculty_City) FROM Faculty_Info WHERE Faculty_Id >1002;  

Output:

Faculty_Id	UPPER(Faculty_First_Name)	UPPER(Faculty_Address)	UPPER(Faculty_City)
1004	SAURABH	SECTOR 128	MUMBAI
1005	SHIVANI	VIVEK VIHAR	KOLKATA
1006	AVINASH	SARVODYA CALONY	DELHI
1007	SHYAM	KRISHNA NAGAR	LUCKNOW