UPPER String Function¶

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.

Sample Uses of the UPPER String Function¶

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) = 'DATABASE 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