Skip Navigation Links
Home
Functions
Tips & Tricks
SQL Server 2005
SQL Server 2008
SQL Server 2012
FAQ
Forums
Practice Test
Bookstore
Tip of the Day : Example Uses of the CHOOSE Logical Function
SQL Server Helper
Home > > Tip of the Day
SQL Server Helper - Tip of the Day

The CHOOSE logical function introduced in SQL Server 2012 returns the item at the specified index from a list of values.  The syntax of the CHOOSE logical function is as follows:

CHOOSE ( < index >, < value_1 >, < value_2 > [ , < val_n > ] )

The < index> is an integer expression that represents a 1-based index into the list of the items following it.  If the provided index value has a numeric data type other than INT, then the value is implicitly converted to an integer.  If the index value exceeds the bounds of the array of values, then the CHOOSE function will return a NULL result.  The < value > parameters are a list of comma-separated values of any data type.

Here are a few uses of the CHOOSE logical function:

Usage #1 : Return the Day of the Week in an Unsupported Language

SELECT CHOOSE( DATEPART(DW, GETDATE()), 
               'Linggo', 'Lunes', 'Martes', 'Miyerkoles', 
               'Huwebes', 'Biyernes', 'Sabado' ) AS [Day of the Week in Tagalog]

Usage #2 : Return the Name of the Month in an Unsupported Language

SELECT CHOOSE ( MONTH(GETDATE()), 'Enero', 'Pebrero', 'Marso', 'Abril', 'Mayo',
                'Hunyo', 'Hulyo', 'Agosto', 'Setyembre', 'Oktubre', 'Nobyembre',
                'Disyembre' ) AS [Month Name in Tagalog]

Usage #3 : Return the Ordinal Name of a Cardinal Number

SELECT [TeamName], CHOOSE ( [Position], 'First', 'Second', 'Third', 'Fourth', 'Fifth' ) AS [OrdinalPosition]
FROM [dbo].[TeamPosition]


SELECT [TeamName], CHOOSE ( [Position], '1st', '2nd', '3rd', '4th', '5th' ) AS [OrdinalPosition]
FROM [dbo].[TeamPosition]

Usage #4 : Translate a Letter to Phonetic Alphabet

DECLARE @Letter     CHAR(1)


SET @Letter = 'S'
SELECT CHOOSE( ASCII(UPPER(@Letter)) - 64, 'Alpha', 'Bravo', 'Charlie', 'Delta', 'Echo', 'Foxtrot', 'Golf',
               'Hotel', 'India', 'Juliett', 'Kilo', 'Lima', 'Mike', 'November', 'Oscar', 'Papa', 'Quebec',
               'Romeo', 'Sierra', 'Tango', 'Uniform', 'Victor', 'Whiskey', 'Xray', 'Yankee', 'Zulu' )
               AS [Phonetic]

Usage #5 : Convert a BIT Value Into Yes/No or True/False String Value

SELECT [UserName], CHOOSE( CAST([IsActive] AS TINYINT) + 1, 'No', 'Yes' ) AS [IsActive]
FROM [dbo].[User]

Back to Tip of the Day List Next Tip