Separating elements

Separating elements is a classic T-SQL challenge. It involves a table called Arrays with strings holding comma-separated lists of values in a column called arr.

Run the following code to create the Arrays table, and populate it with sample data:

SET NOCOUNT ON;USE tempdb;IF OBJECT_ID(N'dbo.Arrays', N'U') IS NOT NULL    DROP TABLE dbo.Arrays;CREATE TABLE dbo.Arrays (      id  VARCHAR(10)    NOT NULL PRIMARY KEY    ,  arr VARCHAR(8000) NOT NULL    );GOINSERT INTO dbo.ArraysVALUES (    'A'    ,'20,223,2544,25567,14'    );INSERT INTO dbo.ArraysVALUES (    'B'    ,'30,-23433,28'    );INSERT INTO dbo.ArraysVALUES (    'C'    ,'12,10,8099,12,1200,13,12,14,10,9'    );INSERT INTO dbo.ArraysVALUES (    'D'    ,'-4,-6,-45678,-2'    );

The challenge, therefore, is to find an efficient set-based solution using T-SQL. To implement such a solution, you will want to split the task into three steps:

1. Generate copies.

2. Extract an element.

3. Calculate the position.

Generate copies

SELECT id    ,arr    ,nFROM dbo.Arrays     INNER JOIN AdventureWorks2014.dbo.Nums             ON n <= LEN(arr) AND SUBSTRING(N','+arr, n, 1) = ',';

This code generates the following output:

Extract an element

SUBSTRING(arr, n, CHARINDEX(',', arr + ',', n) – n).

Calculate the position

SELECT     id    ,ROW_NUMBER() OVER ( PARTITION BY id ORDER BY n) AS pos    ,SUBSTRING(arr, n, CHARINDEX(',', arr + ',', n) - n) AS elementFROM    dbo.Arrays      INNER JOIN AdventureWorks2014.dbo.Nums             ON n <= LEN(arr) + 1 AND SUBSTRING(',' + arr, n, 1) = ',';