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.
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.
Prepare the Table
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 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];
UPPER 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];
UPPER 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.
UPPER in the 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');
The 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.