In SQL Server the CHOOSE() function returns the item at the specified index from a list of values.
Syntax
The syntax goes like this:
CHOOSE ( index, val_1, val_2 [, val_n ] )The first argument is a 1-based index that specifies which of the following values to return.
Example
Here’s a simple example to demonstrate:
SELECT CHOOSE( 2, 'Bird', 'Cat', 'Dog', 'Cow' );Result:
Cat
Here, the first argument is 2 and so the function returned the second value from the list that followed. In this case there are four items in the list, and the second value is Cat.
Notice that it’s a 1-based index, and so the count starts at 1.
Using a Column Value as the Index
It’s possible to use a column name for the first argument. In this case, the column’s value will determine what list item is returned.
Suppose we have the following table:
SELECT * FROM PetTypes;
Result:
PetTypeId PetType --------- ------- 1 Bird 2 Cat 3 Dog 4 Cow
We could pass the PetTypeId column to the CHOOSE() function to produce something like this:
SELECT
PetTypeId,
PetType,
CHOOSE(
PetTypeId,
'Tweet', 'Meow', 'Woof', 'Moo'
) AS "Result"
FROM PetTypes;Result:
PetTypeId PetType Result --------- ------- ------ 1 Bird Tweet 2 Cat Meow 3 Dog Woof 4 Cow Moo
We returned a different value based on the value of the PetTypeId column for the current row.
Dates
If the first argument is a date/time column, we can use CHOOSE() to return a different value, depending on the date.
Example:
SELECT CHOOSE(
MONTH('2030-12-30'),
'Q1','Q1', 'Q1',
'Q2','Q2','Q2',
'Q3', 'Q3','Q3',
'Q4','Q4','Q4') AS "Fiscal Quarter";Result:
Q4
In this case, the date is assigned to one of the fiscal quarters, depending on the month of that date. The date is assigned as follows:
- January, February, and March (Q1)
- April, May, and June (Q2)
- July, August, and September (Q3)
- October, November, and December (Q4)
We can use the same technique for data in a database.
Suppose we have the following table:
SELECT * FROM Dividends;Result:
DeclarationDate ExDate RecordDate PayableDate Amount
---------------- ---------------- ---------------- ---------------- ---------------------
2023-03-08 2023-03-15 2023-03-18 2023-04-03 10.5200
2023-06-08 2023-06-15 2023-06-18 2023-07-03 10.5200
2023-09-08 2023-09-15 2023-09-18 2023-10-03 10.5200
2023-12-08 2023-12-15 2023-12-18 2024-01-03 12.8600
The following query uses CHOOSE() to determine which fiscal quarter certain dates fall under:
SELECT
CHOOSE(
MONTH(ExDate),
'Q1', 'Q1', 'Q1',
'Q2', 'Q2', 'Q2',
'Q3', 'Q3', 'Q3',
'Q4', 'Q4', 'Q4'
) AS "ExDate",
CHOOSE(
MONTH(PayableDate),
'Q1', 'Q1', 'Q1',
'Q2', 'Q2', 'Q2',
'Q3', 'Q3', 'Q3',
'Q4', 'Q4', 'Q4'
) AS "PayableDate",
Amount
FROM Dividends;Result:
ExDate PayableDate Amount ------ ----------- --------------------- Q1 Q2 10.5200 Q2 Q3 10.5200 Q3 Q4 10.5200 Q4 Q1 12.8600
This tells use the fiscal quarter for each dividend’s “ex date”, as well as the fiscal quarter that it will be paid (the PayableDate).
We achieved this by using the MONTH() function to extract the month from the given date. This function returns an integer that represents the month of the specified. So for example, January is 1, February is 2, and so on. Another way to do it is with the DATEPART() function (where we could use DATEPART(month, date) to get the same result, where date is the date we want to extract the month from).
First Argument Type
The first argument needs to be an integer or a type that can be converted to an integer. If it’s not an integer, SQL Server will implicitly convert it to an integer, but if it can’t do this, we’ll get an error.
Example:
SELECT CHOOSE( 'Two', 'Bird', 'Cat', 'Dog', 'Cow' );Result:
Msg 245, Level 16, State 1, Line 1 Conversion failed when converting the varchar value 'Two' to data type int.
The error occurred because I passed a string as the first argument, and that particular string couldn’t be converted to an integer.
However, not all strings are doomed. For example, if our string is merely an integer inside single quotes, SQL Server should be able to convert it to an actual integer:
SELECT CHOOSE( '2', 'Bird', 'Cat', 'Dog', 'Cow' );Result:
Cat
Also, if we have a numeric value that’s not an integer, SQL Server should be able to convert it to an integer:
SELECT CHOOSE( 2.56, 'Bird', 'Cat', 'Dog', 'Cow' );Result:
Cat
However, don’t count on this being the case when the numeric value is enclosed in quotes:
SELECT CHOOSE( '2.56', 'Bird', 'Cat', 'Dog', 'Cow' );Result:
Msg 245, Level 16, State 1, Line 1 Conversion failed when converting the varchar value '2.56' to data type int.