UPPER Function in SQL Server

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.

Table of Contents

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(str_exp)

Where,

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;

UPPER Function in SQL Server Lowercase Literal String

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 ‘james@gmail.com’, here this mail contains the @ characters and the dot (.).

SELECT UPPER('james@gmail.com') AS ToUpperCase;

UPPER Function in SQL Server with Other Characters

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.

UPPER Function in SQL Server with Table CustomerData Column

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.

Conclusion

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.