Overview
The UPPER function is used to make to a string of characters or character expression all uppercase.
Explanation
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 collationThe 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 #tempHere is the output.
Additional Information
Daniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 10 years of experience as a QE and developer for SQL Server related software. He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs.
- MSSQLTips Awards: Author of the Year Contender – 2015-2018, 2022, 2023 | Champion (100+ tips) – 2018
