SQL Server CHOOSE Function

SQL Server CHOOSE Functions is the logical function that returns the item at the specified index from a list of values in SQL Server.

Syntax

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

index
It is an integer expression that represents a 1-based index into the list of the items following it.

If index is 1, then CHOOSE() function returns val_1. If index is 2, the CHOOSE() function returns val_2, and so on.

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.

Lets Look at an example of CHOOSE Function in SQL Server.

Basic use of CHOOSE Function

The following statement uses CHOOSE function and returns the second item from the list of values that is given.


SELECT CHOOSE ( 2, 'Microsoft', 'SQL', 'Server') AS Output;

[Also Read logical function IIF ()]

Using CHOOSE Function with table column

First we create a sample table named QuestionMaster then insert some records into table.

CREATE TABLE QuestionMaster 
(QuestionId INT NOT NULL, AnswerID INT)

INSERT INTO QuestionMaster
VALUES
( 1, 1),(2, 1),(3, 2),(4, 4),(5, 3)

Now we have a table QuestionMaster in Database as shown below.

SELECT * FROM QuestionMaster

Following statement uses the CHOOSE function to return the Answer based on the value in AnswerId column from QuestionMaster table .

SELECT * FROM QuestionMaster

SELECT QuestionId, CHOOSE( AnswerID , 'yes', 'no', 'may be' ,'none') AS Answer

FROM QuestionMaster

As you can see, the for each QuestionId the CHOOSE Functions returns the Answer at the specified index from a specified list of values.

Also Read..

2,231 total views, 1 views today

SQL Server CHOOSE Function