By: Daniel Calbimonte
The UPPER function is used to make to a string of characters or character expression all uppercase.
Syntax
UPPER(expression)
Parameters
- expression - this is the string or expression used that we require to make uppercase.
Simple format example
The following example will make the string all uppercase.
SELECT UPPER('hello world') as message
Example working with columns
The following example will uppercase the firstname of the table Person.
SELECT UPPER([FirstName]) as firstname FROM [AdventureWorks2019].[Person].[Person]
Example comparing strings
By default, SQL Server is case insensitive, but you can change the collation. The following query shows how to check the case sensitiveness.
SELECT SERVERPROPERTY('COLLATION') as collation
The result may be something like this: SQL_Latin1_General_CP1_CI_AS, if it says CI then SQL Server is case insensitive.
If your result is something like this: SQL_Latin1_General_CP1_CS_AS, the CS means SQL Server is case sensitive.
If your SQL Server is case sensitive. In that case, the lower function can be useful to find strings that may or not may be uppercase. The following example shows how to compare strings by doing a uppercase to the column and the string characters in order to force the same case sensitivity.
SELECT BusinessEntityID,FirstName, LastName
FROM [AdventureWorks2019].[Person].[Person]
WHERE UPPER(FirstName) = UPPER('kim')
Proper Case Using UPPER and LOWER Example
Here is an example where we can use UPPER and LOWER to get the proper case for a name. In the example below we create a temp table and load some sample data. We are also using the LEFT function and SUBSTRING function.
CREATE TABLE #temp (FirstName nvarchar(20))
INSERT INTO #temp
VALUES ('kim'), ('sam'), ('adam')
SELECT FirstName, UPPER(LEFT(FirstName,1)) + LOWER(SUBSTRING(FirstName,2,100)) as ProperCase
FROM #temp
Here is the output.
