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
);GO INSERT INTO dbo.Arrays
VALUES (
'A'
,'20,223,2544,25567,14'
); INSERT INTO dbo.Arrays
VALUES (
'B'
,'30,-23433,28'
); INSERT INTO dbo.Arrays
VALUES (
'C'
,'12,10,8099,12,1200,13,12,14,10,9'
); INSERT INTO dbo.Arrays
VALUES (
'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
,n
FROM 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 element
FROM dbo.Arrays
INNER JOIN AdventureWorks2014.dbo.Nums
ON n <= LEN(arr) + 1 AND SUBSTRING(',' + arr, n, 1) = ',';