SQL Server CHOOSE Function

Summary: in this tutorial, you will learn how to use the SQL Server CHOOSE() function to return an item based on its index in a list of values.

SQL Server CHOOSE() function overview

The CHOOSE() function returns the item from a list of items at a specified index.

The following shows the syntax of the CHOOSE() function:

CHOOSE ( index, elem_1, elem_2 [, elem_n ] )
Code language: SQL (Structured Query Language) (sql)

In this syntax:

• The index is an integer expression that specifies the index of the element to be returned. Note that the indexes of the elements are 1-based. It means that the first element has an index of 1, the second element has an index of 2, and so on.
• The elem_1, elem_2,… elem_n is a list of comma-separated values of any type.

If the index is 1, the CHOOSE() function returns elem_1. If the index is 2, the CHOOSE() function returns elem_2, etc.

If index is not an integer, it will be converted to an integer. In case the index is out of the boundary of the list, the CHOOSE() function will return NULL.

SQL Server CHOOSE() function examples

Let’s take some examples of the CHOOSE() function.

A) Using SQL Server CHOOSE() function with literal values example

This example returns the second item from the list of values:

SELECT 
    CHOOSE(2, 'First', 'Second', 'Third') Result;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

Result
------
Second

(1 row affected)
Code language: SQL (Structured Query Language) (sql)

B) Using SQL Server CHOOSE() function for table column example

See the following sales.orders  table from the sample database:

The following example uses the CHOOSE() function to return the order status based on the value in the order_status column of the sales.orders table:

SELECT
    order_id, 
    order_date, 
    status,
    CHOOSE(order_status,
        'Pending', 
        'Processing', 
        'Rejected', 
        'Completed') AS order_status
FROM 
    sales.orders
ORDER BY 
    order_date DESC;
Code language: SQL (Structured Query Language) (sql)

The following picture shows the partial result:

In this example, the status of an order is pending, processing, rejected and completed if the value in the order_status is 1, 2, 3, and 4.

C) Using SQL Server CHOOSE() function with the MONTH function

The following example uses the MONTH() function to return the seasons in which the customers buy products. The result of the MONTH() function is used in the CHOOSE() function to return the corresponding season:

SELECT 
    order_id,
    order_date,
    customer_id,
    CHOOSE(
        MONTH(order_date), 
        'Winter', 
        'Winter', 
        'Spring', 
        'Spring', 
        'Spring', 
        'Summer', 
        'Summer', 
        'Summer', 
        'Autumn', 
        'Autumn', 
        'Autumn', 
        'Winter') month
FROM 
    sales.orders
ORDER BY 
    customer_id;
Code language: SQL (Structured Query Language) (sql)

The following picture shows the partial result set:

In this tutorial, you have learned how to use the SQL Server CHOOSE() function to return an element on its index in a list of values.