In this SQL tutorial, you will learn about the UPPER Function in SQL Server, where you will learn how to transform the string to uppercase.
Additionally, you will learn how to convert literal strings, mixed case strings, and other characters to uppercase using the UPPER() function. Finally, you will use the UPPER() function with table column values.
UPPER Function in SQL Server
The UPPER() function in SQL Server changes the case of the characters in the string to uppercase. In simple terms, if you have a string such as ‘database’, then the UPPER() function converts this string to uppercase like ‘DATABASE’.
In addition, it changes the small letters to capital letters, if the string contains any other characters other than alphabets such as #, &, *, etc. Then the UPPER() function doesn’t convert those characters, and that kind of character remains unchanged.
The syntax of the UPPER() function is given below.
- UPPER(): It is the function to change the string to uppercase.
- str_exp: It is the string expression, and can be characters, strings, constants, variables, or table columns. The data type of the string expression should be implicitly convertible to varchar datatype.
The return type of the UPPER() function is varchar or nvarchar datatype. This means after converting the string expression to uppercase, it returns a string of nvarchar or varchar datatype.
Let’s look at different examples to learn how to use the UPPER() function.
UPPER Function in SQL Server: Lowercase Literal String
If you have a string value in lowercase, then you can change that string to uppercase. For example, you have the string ‘i traveled to the usa’.
To change that string into uppercase use the below query.
SELECT UPPER('i traveled to the usa') AS ToUpperCase;
When the above query is executed, it converts the string in lowercase to uppercase. For example, the string ‘i traveled to the usa‘ is converted to ‘I TRAVELED TO THE USA’ which you can see in the above output.
Upper Function in SQL Server: Mixed-Case
If the string contains both the cases, the uppercase and lowercase characters, the UPPER() function can also convert that string into only uppercase.
Suppose you have a string of mixed-case such as ‘Great America’, as you can see this string contains both uppercase and lowercase letters.
Now use the below query to convert that string to uppercase.
SELECT UPPER('Great America') AS ToUpperCase;
As you can see in the above output of the query, the provided string ‘Great America’ to the UPPER() function is converted to an uppercase string ‘GREAT AMERICA’.
UPPER Function in SQL Server: with Other Characters
The UPPER() function doesn’t change the characters other than alphabets in the string. For example, you have the email address ‘firstname.lastname@example.org’, here this mail contains the @ characters and the dot (.).
SELECT UPPER('email@example.com') AS ToUpperCase;
Look at the above output of the query in the picture, the characters such as @ and dot (.) are not affected by the UPPER() function, but the function converted all the small letters to the capital letter such as ‘JAMES@GMAIL.COM’ that you can see in the above output.
Thus, when the UPPER() function converts the string to uppercase, then special characters with that string remain unchanged or not changed by the UPPER() function.
This is how to use the UPPER() function with a string containing the special characters.
UPPER Function in SQL Server: with Table Column
You can also use the UPPER() function with table columns to convert the column value from lowercase to uppercase.
For example, you have a CustomerData table with columns CustomerID, CustomerName, PhoneNumber, and ProductDescription which is shown below.
Suppose you have to convert the customer name to uppercase which is shown in the above picture in the CustomerName column.
For that use the below query.
SELECT CustomerName, UPPER(CustomerName) AS UpperCaseName FROM CustomerData;
As you can see from the above output, the customer name in the column CustomerName is converted to uppercase. For example customer named Emily Turner converted to uppercase EMILY TURNER and the same for the other customer’s name.
This is how to use the UPPER() function in SQL Server with table columns.
In this SQL Server tutorial, you learned how to use the UPPER() function in SQL Server to convert the given string value to uppercase in SQL Server. Also, you have used the UPPER() function with literal string, other special characters, mixed-case string, and on the table column value to convert them into uppercase.
You may like to read:
After working for more than 15 years in the Software field, especially in Microsoft technologies, I have decided to share my expert knowledge of SQL Server. Check out all the SQL Server and related database tutorials I have shared here. Most of the readers are from countries like the United States of America, the United Kingdom, New Zealand, Australia, Canada, etc. I am also a Microsoft MVP. Check out more here.