T-SQL Recipes之Separating elements

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) = ',';