Understanding of SQL Server UPPER Function

There are many cases of SQL lowercase that we need to change for our purposes. For instance, we have keywords or strings of characters in the lowercase that we have to convert to capital letters. To do it quickly and accurately, we can use the UPPER function in the SQL Server.

This function converts character expressions from lowercase to uppercase. It works when either an ad hoc string is passed to the function or a column name is passed.

Have a look at the example below. We are passing a simple string to the function, and it returns the upper case of the expression.

Now, let us explore the scope and work of this function more thoroughly.

SQL UPPER Function By Practical Example

To start our examination of the SQL uppercase usage, let’s first prepare a very small table with four columns. The first column is the ID column. All other columns are character columns – char, varchar, and nvarchar respectively.

We create the table and populate it using the codes in Listing 1.

-- Listing 1: Prepare a Table
use AU
go
create table [character] (
ID int identity(1,1)
,namechar char(10)
,namevarchar varchar(10)
,namenvarchar nvarchar(10)
);

use AU
go
insert into [character] values ('Kenneth','Kenneth','Kenneth');
insert into [character] values ('Amos','Amos','Amos');
insert into [character] values ('Amanda','Amanda','Amanda');
insert into [character] values ('Joana','Joana','Joana');
insert into [character] values ('Hassan','Hassan','Hassan');

When we query this table, we get the output in Figure 3. All names in the table have only the first letter in the upper case.

-- Listing 2: Query the Table
use AU
go
select ID, namechar, namevarchar, namenvarchar from [character];

Use an SQL UPPER to Convert Values of All Columns to Uppercase

Using a slightly modified version of the Listing 1 query, we can generate the output shown in the previous Figure 2. This modified code is present in Listing 3 – the query introduces the UPPER() function that converts values of all columns in SQL to UPPER case.

As with all functions, the input of the column names in the table comes in brackets.

-- Listing 3: Prepare a Table
use AU
go
select ID, UPPER(namechar) AS namechar, UPPER(namevarchar) AS namevarchar, UPPER(namenvarchar) AS namevarchar from [character];

Use an SQL UPPER Function in the WHERE Clause

Have a look at the below code in Listings 4a and 4b – the code is not identical in them.

The queries first ask our SQL Server to convert the value is in the namechar column (whatever it is) to the UPPER case, and then to compare it to the predicate. If there is a match, the rows are returned.

But we see something unexpected here: the queries return the exact result set as shown in Figure 5. It is unexpected because the predicate is in the UPPER case in Listing 4a, while the same predicate is in the lower case in Listing 4b.

-- Listing 4: Upper in the WHERE Clause
-- Listing 4a
use AU
go
select ID, UPPER(namechar) AS namechar, UPPER(namevarchar) AS namevarchar, UPPER(namenvarchar) AS namevarchar 
from [character]
where UPPER(namechar)='KENNETH';

-- Listing 4b
use AU
go
select ID, UPPER(namechar) AS namechar, UPPER(namevarchar) AS namevarchar, UPPER(namenvarchar) AS namevarchar 
from [character]
where UPPER(namechar)='Kenneth';

The reason is that the SQL Server collation is set to SQL_Latin1_General_CP1_CI_AS. CI stands for Case Insensitive.

The collation in SQL Server represents the character set and encoding rules. If we alter the collation of the namechar column using the code in Listing 5, we get a different result for executing the code in Listing 4 (see Figure 6).

-- Listing 5: Change Column Collation
ALTER TABLE [character]
ALTER COLUMN namechar VARCHAR(50) COLLATE SQL_Latin1_General_CP1_CS_AS;

All we needed was to change the SQL Server collation to case sensitive.

Collation can also be changed at the database level. However, we applied changes at the table level to keep it simple.

Case Insensitive Search Using SQL Server UPPER Function

The code presented in Listing 6 flips the question we are asking the SQL Server.

In this case, we expect that the predicate gets converted to the SQL proper case, which is the UPPER case before being compared to the value in the namechar column.

Thus, we consider the following:

• if the instance/database/table is case sensitive (collation SQL_Latin1_General_CP1_CS_AS), the query will return no rows;
• if the instance/database/table is case insensitive (collation SQL_Latin1_General_CP1_CI_AS), the query will return one row.

-- Listing 6: Upper in the WHERE Clause
use AU
go
select ID, UPPER(namechar) AS namechar, UPPER(namevarchar) AS namevarchar, UPPER(namenvarchar) AS namevarchar 
from [character]
where namechar=UPPER('Kenneth');

Conclusion

The UPPER function converts character strings to upper case whether they are explicitly provided or they occur in the column of a table. There is also the method to perform the opposite task, convert upper to lowercase, but we’ll examine it in future articles.

The results returned by the function depend on the collation of the instance/database/table. UPPER (as well as LOWER) is very useful in WHERE clauses when the instance is case sensitive.

However, the nature of the data can be unknown, and DBAs will want to ensure that the right comparison is done.