CHOOSE (Transact-SQL)
Returns the item at the specified index from a list of values in SQL Server 2012.
.gif "Topic link icon")
Transact-SQL Syntax Conventions
Syntax
CHOOSE ( index, val_1, val_2 [, val_n ] )
Arguments
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 CHOOSE returns null.
val_1 … val_n
List of comma separated values of any data type.
Return Types
Returns the data type with the highest precedence from the set of types passed to the function. For more information, see Data Type Precedence (Transact-SQL).
Remarks
CHOOSE acts like an index into an array, where the array is composed of the arguments that follow the index argument. The index argument determines which of the following values will be returned.
Examples
The following example returns the third item from the list of values that is provided.
SELECT CHOOSE ( 3, 'Manager', 'Director', 'Developer', 'Tester' ) AS Result;
Here is the result set.
Result
-------------
Developer
(1 row(s) affected)
The following example returns a simple character string based on the value in the ProductCategoryID column.
USE AdventureWorks2012;
GO
SELECT ProductCategoryID, CHOOSE (ProductCategoryID, 'A','B','C','D','E') AS Expression1
FROM Production.ProductCategory;
Here is the result set.
ProductCategoryID Expression1
----------------- -----------
3 C
1 A
2 B
4 D
(4 row(s) affected)
The following example returns the quarter in which an employee was hired. The MONTH function is used to return the month value from the column HireDate.
USE AdventureWorks2012;
GO
SELECT JobTitle, HireDate, CHOOSE(MONTH(HireDate),'Winter','Winter', 'Spring','Spring','Spring','Summer','Summer',
'Summer','Autumn','Autumn','Autumn','Winter') AS Quarter_Hired
FROM HumanResources.Employee
WHERE YEAR(HireDate) > 2005
ORDER BY YEAR(HireDate);
Here is the result set.
JobTitle HireDate Quarter_Hired
-------------------------------------------------- ---------- -------------
Sales Representative 2006-11-01 Autumn
European Sales Manager 2006-05-18 Spring
Sales Representative 2006-07-01 Summer
Sales Representative 2006-07-01 Summer
Sales Representative 2007-07-01 Summer
Pacific Sales Manager 2007-04-15 Spring
Sales Representative 2007-07-01 Summer