SQL Server按逗号拆分data_key列为5列的SELECT查询求助

你原有代码出错的核心原因是拆分第3、4列时，没有正确定位第2、第3个逗号的位置，也没有正确计算截取长度。建议先预计算4个逗号的坐标后再拆分，逻辑更清晰也不容易出错，可用的SQL代码如下：

SELECT TOP 1000
    LEFT(data_key, c1 - 1) AS start,
    SUBSTRING(data_key, c1 + 1, c2 - c1 - 1) AS two,
    SUBSTRING(data_key, c2 + 1, c3 - c2 - 1) AS three,
    SUBSTRING(data_key, c3 + 1, c4 - c3 - 1) AS four,
    RIGHT(data_key, CHARINDEX(',', REVERSE(data_key)) - 1) AS five,
    data_key
FROM (
    -- 预计算四个逗号的所在位置
    SELECT 
        data_key,
        CHARINDEX(',', data_key) AS c1,
        CHARINDEX(',', data_key, CHARINDEX(',', data_key) + 1) AS c2,
        CHARINDEX(',', data_key, CHARINDEX(',', data_key, CHARINDEX(',', data_key) + 1) + 1) AS c3,
        CHARINDEX(',', data_key, CHARINDEX(',', data_key, CHARINDEX(',', data_key, CHARINDEX(',', data_key) + 1) + 1) + 1) AS c4
    FROM [table]
) AS pos_calc

如果你不想使用子查询，也可以直接把坐标计算嵌套到SUBSTRING函数中，写法如下：

SELECT TOP 1000
LEFT(data_key, CHARINDEX(',', data_key)-1) start,
SUBSTRING(data_key, CHARINDEX(',', data_key)+1, CHARINDEX(',', data_key, CHARINDEX(',', data_key)+1) -  CHARINDEX(',', data_key) -1 )  two, 
-- 第三列：从第2个逗号后开始，截取长度为第3个逗号位置减第2个逗号位置减1
SUBSTRING(data_key, CHARINDEX(',', data_key, CHARINDEX(',', data_key)+1)+1, CHARINDEX(',', data_key, CHARINDEX(',', data_key, CHARINDEX(',', data_key)+1)+1) - CHARINDEX(',', data_key, CHARINDEX(',', data_key)+1) -1 ) three,
-- 第四列：从第3个逗号后开始，截取长度为第4个逗号位置减第3个逗号位置减1
SUBSTRING(data_key, CHARINDEX(',', data_key, CHARINDEX(',', data_key, CHARINDEX(',', data_key)+1)+1)+1, CHARINDEX(',', data_key, CHARINDEX(',', data_key, CHARINDEX(',', data_key, CHARINDEX(',', data_key)+1)+1)+1) - CHARINDEX(',', data_key, CHARINDEX(',', data_key, CHARINDEX(',', data_key)+1)+1) -1 ) four,
RIGHT(data_key, CHARINDEX(',', REVERSE(data_key))-1) five, data_key
FROM [table]

内容的提问来源于stack exchange，提问作者Tom Longworth