CHOOSE Function in SQL Server

In this SQL Server tutorial, I will show you how to use the CHOOSE function in SQL Server; this function helps in complex decision-making logic and simplifies the data retrieval process.

As a database developer, you must know how to use this logical function, which resembles the workings of a CASE statement. But it is a more simplified version of the CASE statement.

Here, I will explain the CHOOSE() function with an example showing how it works. After that, I will explain the syntax of this function with practical examples.

As this function works on the concept of index, here I will use how you can use the different types of index values in the CHOOSE() function. Additionally, you will learn how to use this function with a table.

Table of Contents

The CHOOSE Function in SQL Server returns the element from the list based on the given index value.

Let’s say you have a list of values separated by commas like (“USA”, “Australia”,
“Canada”), to get any value from this list, specify index as 1; as a result, CHOOSE Function returns the first element, which is “USA”; if specify index as 2, it returns “Austrailia”, and for index 3, returns “Canada”.

This is how the CHOOSE function works; based on the index, it returns the value from the list of values.

The syntax is given below.

CHOOSE ( index, val_1, val_2 [, val_n ] )

Where,

CHOOSE(): The function name returns the item from the list based on the index.
index: The integer value represents the index of the items or elements in the given list. The index starts from 1, so the first element or value in the list will have an index of 1, the second will have an index of 2, and so on.
- If you specify any value other than an integer as an index, that value will be converted to an integer implicitly (internally).
val_1, val_2 [,val_n]: A list of values or times returned based on the index. Commas separate these lists of values and can be of any data type.

Now, let’s see with a simple example how the CHOOSE function in SQL Server returns the value based on the index.

For example, you have a list of values like “Chicago”, “Los Angeles”, and “New York”, and you need to find the value at index 2. As shown below, you can pass this list of values to the CHOOSE() function and specify the index as 2.

SELECT CHOOSE(2,'Chicago', 'Los Angeles', 'New York') AS IndexValue;

The query returns the result containing the string value ‘Los Angeles’, the value in the list at the 2nd index.

One thing to note here is that the index of the list starts from the value 1, which means the string ‘Chicago’ in the list has an index of 1, and so on for other string values in the list.

This is how to use the CHOOSE function in SQL Server. Next, pass the different index values, such as float values.

Using CHOOSE Function in SQL Server with Float

In the examples above, you have learned how to pass the index value as an integer to the CHOOSE function in SQL Server, which returns the items from the list based on that index.

What will happen if you pass the float value as an index instead of an integer value? let’s see with an example.

Suppose you have a list of integers valued as 2, 6, 9, 1, and if you need to get the value from the list based on the index value, which is 2.4, execute the query below.

SELECT CHOOSE(2.4, 2, 6, 9, 1) AS IndexValue;

Using CHOOSE Function in SQL Server with Float

From the output, the element at index 2.4 is 6, But here, we have specified index 2.4, which doesn’t exist.

Because the index in the list starts from 1, 2, and 3, so for the elements. But how does the CHOOSE function process the index value that is not an integer?

Here, the float value of 2.4 is rounded to the nearest low value of 2; then, this float value is converted to an integer value. So internally, it considers the index value 2.4 as 2; that is the reason it returns the element of the 2nd index in the list.

If you specify the index value as 2.8, the CHOOSE() function converts this value to 2; for a 3.7 index value, it will convert to 3, and the same concept is applied for all kinds of float values passed as an index.

This is how to pass the float type index value in the CHOOSE function in SQL Server.

Using CHOOSE Function in SQL Server with String

You have passed the index value as an integer and float, but what will happen if you pass the index value as a string?

Let’s see with an example you have a list of elements such as ‘USA’, ‘Canada’, and ‘Brazil’, and you pass the index as a string value like ‘3’ to CHOOSE() function as shown below.

SELECT CHOOSE('3', 'USA','Canada','Brazil') AS IndexValue;

Using CHOOSE Function in SQL Server with String

When you pass the index as ‘3’, the string value, the CHOOSE() function still works and returns the element ‘Brazil’ from the list.

Here, whatever the value you pass to the CHOOSE() function is, if the value is an integer, it is okay; otherwise, if it is not an integer, then that value is converted into an integer implicitly.

The index value you passed to the function was string ‘3’; first, this value is converted into an integer, and then the element at that index is returned from the list.

This is how to use the string value as an index in the CHOOSE function in SQL Server.

Using CHOOSE Function in SQL Server with table

You have used the CHOOSE function with some list of values separated by commas. I will show how to use the CHOOSE() function with the table column. For example, you have a StudentScores table, as shown below.

Using CHOOSE Function in SQL Server with StudentScore Table

Using the above table, you have a task to categorize students based on their average marks into grades A, B, C, or F. Here, you can use the CHOOSE() function as shown below.

SELECT StudentID, AverageMarks,
CHOOSE((AverageMarks/20), 'F', 'C', 'B', 'A') AS StudentGrade
FROM StudentScores;

Using CHOOSE Function in SQL Server with table

From the output of the above query, each student got graded based on their average marks. For example, students with IDs equal to 4 and average marks equal to 85 have grade A.

Now, let’s understand the query part, CHOOSE((AverageMarks/20), ‘F’, ‘C’, ‘B’, ‘A’), here (AvarageMarks/20) is used as an index to select a grade from the list ‘F’, ‘C’, ‘B’, ‘A’.

For example, if AverageMarks is 85, divide this mark by 20, and you get the value 4.2; when this value is passed as an index to the CHOOSE() function, then it rounds the value to 2, and in return, the grade value of index 2 which is ‘A’ in this case.

In the same way, it computes the grade for all the other students. This is how to use the CHOOSE function in SQL Server with a table.

To know more about the CHOOSE() function, visit the tutorial, Logical Function-CHOOSE.

Conclusion

In this SQL Server tutorial, you learned how to retrieve the item, element or value from the list of values based on the index value using the CHOOSE function in SQL Server.

You have used the different types of index values, such as integer, float and string, and learned how the CHOOSE() function implicitly converts some non-integer values to integer values.