Skip Navigation Links
Home
Articles
SQL Server 2012
SQL Server 2014
SQL Server 2016
FAQ
Forums
Practice Test
Bookstore
Tip of the Day : Example Uses of the UPPER String Function
SQL Server Helper
Home > > Tip of the Day
SQL Server Helper - Tip of the Day

The UPPER string function returns a character expression with lowercase character data converted to uppercase. The syntax of the UPPER string function is as follows:

UPPER ( <character_expression> )

The <character_expession> parameter of the UPPER string function is an expression of character data which can be a constant, variable, or column of either character or binary data.  It must be of a data type that is implicitly convertible to VARCHAR data type.

Here are a few examples on the uses of the UPPER string function:

Usage #1 : Determine if All Characters in a String are in Upper Case

DECLARE @Input			VARCHAR(20) = 'SQL SERVER HELPER'

SELECT CASE WHEN UPPER(@Input) = @Input COLLATE Latin1_General_BIN
            THEN 'Yes'
            ELSE 'No' END AS [IsAllUpperCase]

Usage #2 : Format a String into Proper Case / Title Case (Equivalent to Oracle’s INITCAP Function)

DECLARE @Input			VARCHAR(100) = 'THE QUICK BROWN FOX JUMPS OVER THE LAZY DOG'

SET @Input = STUFF(LOWER(@Input), 1, 1, UPPER(LEFT(@Input, 1)))
SELECT @Input = REPLACE(@Input, SUBSTRING(@Input, [Number], 2), 
				CASE WHEN SUBSTRING(@Input, [Number], 2) LIKE ' %'
					 THEN UPPER(SUBSTRING(@Input, [Number], 2))
					 ELSE SUBSTRING(@Input, [Number], 2) END)
FROM [master].[dbo].[spt_values]
WHERE [Type] = 'P' AND [Number] != 0 AND [Number] <= LEN(@Input)

SELECT @Input AS [InitCap]
InitCap
-----------------------------------------------
The Quick Brown Fox Jumps Over The Lazy Dog

Back to Tip of the Day List Next Tip