Tutorial Gateway

SQL CHOOSE Function

by

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:

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

SQL CODE

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 1

SQL CHOOSE Function Index Out of Range

In this example, we are going to use the CHOOSE function with index value 0, negative index values, and non-indexing values.

SQL CODE

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

SQL CHOOSE function 2

ANALYSIS

Within this choose function example, the below statements is returning NULL value because the index value should start with One or greater than 1

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

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

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 3

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

SELECT CHOOSE('1', 'Cherry', 'Orange', 'Kiwi', 'Apple') AS Result1;

SELECT CHOOSE('3', 'Cherry', 'Orange', 'Kiwi', 'Apple') AS Result2;

OUTPUT

SQL CHOOSE function 4

Here SQL Server is unable to convert the Varchar value one to integer data type so, it is throwing error.

SQL CODE

SELECT CHOOSE('one', 'Cherry', 'Orange', 'Kiwi', 'Apple') AS Result3;

OUTPUT

SQL CHOOSE function 5

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

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 CHOOSE function 6

SQL CODE

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

SQL CHOOSE function 7

Thank You for Visiting Our Blog

Placed Under: SQL