The SQL CHOOSE function is the new built-in Logical function introduced in SQL Server 2012. This function returns the item at the specified index from the list of items. Let us see the syntax of the SQL Server CHOOSE function:
SQL CHOOSE Logical Function Syntax:
The syntax of the CHOOSE in SQL Server is as follows:
|
1 |
CHOOSE (Index, Value1, Value2, ....,ValueN) |
Index: Please specify the index position of the required item. Index position should start from 1.
- If the specified index value has a numeric data type other than integer, then the numeric value will be implicitly converted to integer type. For example, 10.68 will be converted to 10.
- If there are no items in the specified index, or index out of range, then CHOOSE function will retun NULL value
Value1, Value2…,ValueN are the array of items
SQL CHOOSE Function example
This example will show you the working functionality of CHOOSE() function in SQL Server 2014
SQL CODE
|
1 2 3 4 5 6 7 |
SELECT CHOOSE(1, 'Apple', 'Orange', 'Kiwi', 'Cherry') AS Result1; SELECT CHOOSE(2, 'Apple', 'Orange', 'Kiwi', 'Cherry') AS Result2; SELECT CHOOSE(3, 'Apple', 'Orange', 'Kiwi', 'Cherry') AS Result3; SELECT CHOOSE(4, 'Apple', 'Orange', 'Kiwi', 'Cherry') AS Result4; |
OUTPUT
SQL CHOOSE Function – Index Out of Range
In this example, we are going to use the SQL Server CHOOSE function with index value 0, negative index values, and non-indexing values.
SQL CODE
|
1 2 3 4 5 |
SELECT CHOOSE(0, 'Apple', 'Orange', 'Kiwi', 'Cherry') AS Result1; SELECT CHOOSE(-2, 'Apple', 'Orange', 'Kiwi', 'Cherry') AS Result2; SELECT CHOOSE(6, 'Apple', 'Orange', 'Kiwi', 'Cherry') AS Result3; |
OUTPUT
ANALYSIS
Below statements is returning NULL value because the index value should start with One or greater than 1
|
1 2 3 |
SELECT CHOOSE(0, 'Apple', 'Orange', 'Kiwi', 'Cherry') AS Result1; SELECT CHOOSE(-2, 'Apple', 'Orange', 'Kiwi', 'Cherry') AS Result2; |
Below statement is returning NULL value because we are passing the index value as six, and we don’t have any item at sixth position
|
1 |
SELECT CHOOSE(6, 'Apple', 'Orange', 'Kiwi', 'Cherry') AS Result3; |
SQL CHOOSE Function – Decimal values
In this example, we are going to use the SQL Server CHOOSE function with numeric values. Here, decimal values will be rounded to integer, it means 1.95 will be rounded to 1, 2.05 and 2.99 will be rounded to 2.
SQL CODE
|
1 2 3 4 5 |
SELECT CHOOSE(1.95, 'Cherry', 'Orange', 'Kiwi', 'Apple') AS Result1; SELECT CHOOSE(2.05, 'Cherry', 'Orange', 'Kiwi', 'Apple') AS Result2; SELECT CHOOSE(2.99, 'Cherry', 'Orange', 'Kiwi', 'Apple') AS Result3; |
OUTPUT
SQL CHOOSE Function – String values
In this example, we are going to use the SQL Server CHOOSE function with string values. Here varchar 1 and 3 will be converted to integer data type.
SQL CODE
|
1 2 3 |
SELECT CHOOSE('1', 'Cherry', 'Orange', 'Kiwi', 'Apple') AS Result1; SELECT CHOOSE('3', 'Cherry', 'Orange', 'Kiwi', 'Apple') AS Result2; |
OUTPUT
Here SQL Server is unable to convert the Varchar value one to integer data type so, it is throwing error.
SQL CODE
|
1 |
SELECT CHOOSE('one', 'Cherry', 'Orange', 'Kiwi', 'Apple') AS Result3; |
OUTPUT
SQL CHOOSE Function – Practical example
In this example we are using the AdventureWorks database and the query we used to extract the data is as shown below:
SQL CODE
|
1 2 3 4 5 6 7 8 |
SELECT TOP 10 [NationalIDNumber] ,[LoginID] ,[JobTitle] ,[BirthDate] ,[Gender] ,[HireDate] FROM [AdventureWorks2014].[HumanResources].[Employee] |
Following screenshot will show you the data inside the table. In this example, we will use SQL server CHOOSE function to convert the Month numbers to January, February so on.
SQL CODE
|
1 2 3 4 5 6 7 8 9 10 11 |
SELECT [NationalIDNumber] ,[LoginID] ,[JobTitle] ,[BirthDate] ,CHOOSE(MONTH([BirthDate]),'January','February','March','April','May','June', 'July','August','September','October','November','December') AS Birth_Month ,[Gender] ,[HireDate] ,CHOOSE(DATEPART(MM, [HireDate]),'January','February','March','April','May','June', 'July','August','September','October','November','December') AS Hire_Month FROM [SQL Tutorial].[dbo].[SQL Choose] |
From the above code snippet you can observe that, we can extract the Month number using the built-in function MONTH and DATEPART that’s why we used both the functions
OUTPUT
Thank You for Visiting Our Blog
Leave a Reply