SQL Server is a powerful database management system that offers various features and functionalities to handle data efficiently. In this article, we will delve into the concepts of arrays and string manipulation in SQL Server.

What is an Array?

An array is a fixed-sized, sequentially ordered collection of elements of any data type. It can be considered as a collection of operators or variables. In SQL Server, arrays can be processed using FOR loops and WHILE loops because all the elements in the array are of the same type, and the size of the array is known.

Let’s take a look at an example of declaring an array in SQL Server:

DECLARE @Array VARCHAR(100) = 'Nisarg, Nirali, Dixit, Bharti'
SELECT @Array

The output of the above code will be:

Nisarg, Nirali, Dixit, Bharti

Converting a String into an Array

There are several methods and functions available in SQL Server to convert a string into an array. Let’s explore a few of them:

Method 1: Using STRING_SPLIT Function

The STRING_SPLIT function splits a string into multiple substrings based on a specified delimiter and returns the result as a table. Here’s an example:

DECLARE @InputString VARCHAR(100) = 'Microsoft-SQL-Server'
SELECT value FROM STRING_SPLIT(@InputString, '-')

The output of the above code will be:

Microsoft
SQL
Server

Method 2: Using XML and CROSS APPLY

Another way to split a string into an array is by using XML and the CROSS APPLY operator. Here’s an example:

DECLARE @InputString VARCHAR(100) = 'Microsoft-SQL-Server'
SELECT Split.a.value('.', 'VARCHAR(100)') AS Value
FROM (SELECT CAST('' + REPLACE(@InputString, '-', '') + '' AS XML) AS String) AS A
CROSS APPLY String.nodes('/x') AS Split(a)

The output of the above code will be the same as the previous example:

Microsoft
SQL
Server

Accessing Elements in an Array

To access specific elements in an array, you can use the index of the array. In SQL Server, the index of an array starts from 1. Here’s an example:

DECLARE @InputString VARCHAR(100) = 'Sonali Bhatt is Database Administrator'
DECLARE @CharArray TABLE (Value VARCHAR(100))
INSERT INTO @CharArray
SELECT value FROM STRING_SPLIT(@InputString, ' ')

SELECT Value FROM @CharArray WHERE Value IN ('Sonali', 'Administrator')

The output of the above code will be:

Sonali
Administrator

Conclusion

In this article, we explored the concepts of arrays and string manipulation in SQL Server. We learned how to declare and process arrays, as well as how to convert a string into an array using various methods and functions. Understanding these concepts can greatly enhance your ability to work with data in SQL Server.