Built-in Logical Function CHOOSE in SQL Server

CHOOSE is a built-in logical function introduced in SQL Server 2012.

The function returns the value from the list specified by index.

Here are some examples:

The index of 0 will return NULL value:
SELECT CHOOSE (0, 'January','February','March','April','May','June','July','August','September','October','November','December')

choose_index_0

The index of 1 will return the first value from the list:

SELECT CHOOSE (1, 'January','February','March','April','May','June','July','August','September','October','November','December')

choose_index_1

The index of 5 will return the fifth value from the list:

SELECT CHOOSE (5, 'January','February','March','April','May','June','July','August','September','October','November','December')

choose_index_5

If the index value exceeds the number of items in the list, the function will return NULL:

SELECT CHOOSE (13, 'January','February','March','April','May','June','July','August','September','October','November','December')

choose_index_too_big

Numeric index value other than int is implicitly converted to an integer:

SELECT CHOOSE (2.8, 'January','February','March','April','May','June','July','August','September','October','November','December')

choose_index_not_int

CHOOSE function can be used inside a query. Here is an example:

SELECT [PurchaseOrderDetailID]
,[DueDate]
,[LineTotal]
,CHOOSE(DATEPART(mm, DueDate), 'January','February','March','April','May','June'
,'July','August','September','October','November','December') as 'Month'
FROM [AdventureWorks].[Purchasing].[PurchaseOrderDetail]

choose_index_query

If the post helped you, please share it:
Pin It

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">