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