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');
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.Tags: sql functions, sql server, sql upper Last modified: November 03, 2022