SQL Server UPPER Function


By:

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
SIMPLE T-SQL example

Example working with columns

The following example will uppercase the firstname of the table Person.

SELECT UPPER([FirstName]) as firstname
FROM [AdventureWorks2019].[Person].[Person]
select with UPPER

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')
UPPER in a table column

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.

query results

Related Articles


Last Update: 11/29/2021




Comments For This Article





download














get free sql tips
agree to terms