When working with arrays in SQL Server, there are functions available that can help extract specific values or their positions within the array. Two such functions are ELT() and FIELD(). Let’s dive into these functions and understand how they can be used.

ELT() Function

The ELT() function in SQL Server accepts multiple arguments, with the first argument being the position number. It returns the value at the specified position within the array.

For example, consider the following query:

SELECT ELT(3, 'SQL', 'ORACLE', 'My SQL', 'SQL SERVER') AS ColumnName;

The above query will return “My SQL” as the result. It retrieves the value at the 3rd position from the set of values provided.

If the first argument is 0, less than zero, or greater than the total number of arguments, the ELT() function will return NULL. For instance:

SELECT ELT(0, 'SQL', 'ORACLE', 'My SQL', 'SQL SERVER') AS ColumnName;
SELECT ELT(31, 'SQL', 'ORACLE', 'My SQL', 'SQL SERVER') AS ColumnName;
SELECT ELT(-5, 'SQL', 'ORACLE', 'My SQL', 'SQL SERVER') AS ColumnName;

All of the above queries will return NULL as the result.

FIELD() Function

The FIELD() function in SQL Server is the opposite of the ELT() function. It accepts multiple arguments and returns the position of the specified value within the array.

Consider the following query:

SELECT FIELD('My SQL', 'SQL', 'ORACLE', 'My SQL', 'SQL SERVER') AS ColumnName;

The above query will return 3 as the result. The value “My SQL” is available at the 3rd position in the array.

If the specified value is not found in the array, the FIELD() function will return 0. For example:

SELECT FIELD('COMPUTER', 'SQL', 'ORACLE', 'My SQL', 'SQL SERVER') AS ColumnName;

The result of the above query will be 0.

Have you ever used these functions in your business logic? If yes, we would love to hear about your experiences and how you utilized them.