SQL CHOOSE() Function: Complete Guide
SQL CHOOSE() function introduced in SQL Server 2012. This SQL Choose function returns the item at the specified index from the list of items.
SQL CHOOSE() is a built-in function used to return an item from the list of items based on a specified index. The CHOOSE() function returns the item from a list of items at a specified index.
CHOOSE (Index, Value1, Value2, ...., ValueN);
- Index: It is an expression integer that specifies the index of the element to be returned. Here, indexing is based on 1-based.
- Value1, Value2, …., ValueN: It denotes the list of items separated by commas.
- Value1 will be returned if the index specified is 1, and Value2 will be returned if the index specified is 2 and so on.
- If the index is not in the integer format, then it will be type-casted to an integer.
- If the index is out of bounds, then it will return NULL.
Select CHOOSE (1, ‘App’, ‘Dividend’, ‘Com’);
The index specified was 1, and the App was indexed at the first position. So, the function returned the App as the output.
Select CHOOSE (0, ‘App’, ‘Dividend’, ‘Com’);
Here, the index specified was 0, and as it was out of bounds, so the NULL value was returned.
Select CHOOSE (2.3, ‘App’, ‘Dividend’, ‘Com’);
Here, an index that was specified at decimal representation was type-casted to an integer data type, which rounded off the value to 2. So, Dividend, which was indexed at 2nd position, was returned.
Select CHOOSE (‘3’, ‘App’, ‘Dividend’, ‘Com’);
Here, an index that was specified at varchar was type-casted to the integer data type. So, Com, which was indexed at 3rd position, was returned.
That’s it for this tutorial.