Understanding of SQL Server UPPER Function

The UPPER function is the SQL Server function that converts character expressions to upper case. It works when either an ad hoc string is passed to the function or a column name is passed.

CodingSight - Understanding of SQL Server UPPER Function

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.

Upper function at work
Figure 1: UPPER() Function at Work
UPPER function at work
Figure 2: UPPER() Function at Work II

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

Create Table in SQL

To start our examination, let’s 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 table in SQL 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];
Output of listing
Figure 3: Output of Listing 2

SQL UPPER Function in the Column List

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 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];
Output of listing
Figure 4: Output of Listing 3

SQL Server UPPER in 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';
Output of listing
Figure 5: Output of Listing 4

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;
Output of listing after collation change
Figure 6: Output of Listing 5 after Collation Change

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.

SQL UPPER Function in Predicate

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 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

SQL UPPER function converts character strings to upper case whether they are explicitly provided or they occur in the column of a table.

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.

Kenneth Igiri

Kenneth Igiri

Kenneth Igiri is an Enterprise Architect with eProcess International S.A., Ecobank Group's Shared Services Centre. Kenneth has over fifteen years' experience in Information Technology with nine of those years focused on SQL Server and Oracle databases. His interests include Database Performance, HADR, Cloud Computing, Data Architecture and Enterprise Architecture. Asides from work, Kenneth teaches at Children's Church, writes faith-based fiction and helps small businesses grow. You can connect with Kenneth via his blog https://kennethigiri.com, LinkedIn, or on Amazon.com.

Leave a Reply

Your email address will not be published. Required fields are marked *