SQL Server UPPER() Function

In SQL Server, the UPPER() function is used to convert a string to uppercase letters. It is a built-in character function that can be used on any text field or value.

Syntax

The syntax of the UPPER() function is as follows:

UPPER(string_expression)

Here, string_expression is the string expression to be converted to uppercase letters.

Usage

The UPPER() function is commonly used in the following scenarios:

• In SQL queries, when comparing text, the UPPER() function is used to convert all text to uppercase letters to avoid case sensitivity issues.
• In applications, the UPPER() function can be used to convert user input text to uppercase letters to ensure consistency in input text format.

Examples

Here are two examples of using the UPPER() function:

Example 1

Suppose there is a table named students with the following data:

Now we need to query students with the gender of “MALE”. The following query can be used:

SELECT * FROM students WHERE UPPER(gender) = 'MALE'

This will return all students with the gender of “Male”.

Example 2

Suppose we need to verify whether a user input email address already exists in the database in an application. The following code can be used:

string email = "[email protected]";
string query = "SELECT COUNT(*) FROM users WHERE UPPER(email) = UPPER(@Email)";

using (SqlConnection connection = new SqlConnection(connectionString))
{
    using (SqlCommand command = new SqlCommand(query, connection))
    {
        command.Parameters.AddWithValue("@Email", email);
        connection.Open();
        int count = (int)command.ExecuteScalar();
        if (count > 0)
        {
            Console.WriteLine("This email address is already registered.");
        }
        else
        {
            Console.WriteLine("This email address is available.");
        }
    }
}

This will query whether a user with the same email address (case-insensitive) already exists in the database.

Conclusion

The UPPER() function is a simple and useful function that can conveniently convert any string to uppercase letters. It is commonly used in SQL queries to compare text or in applications to ensure consistency in input text format.