SQL Server 分隔符字符串拆解技巧：CHARINDEX + SUBSTRING 循環/遞歸提取多值

場景

變量 @str = '462,464,2'，需提取第一個值、中間值、最後一個值。適用於不固定長度、不固定個數的分隔符字符串解析。

基礎函數組合

DECLARE @str NVARCHAR(50) = '462,464,2';

-- 字符串長度
SELECT LEN(@str);  -- 9

-- 第一個逗號位置
SELECT CHARINDEX(',', @str, 1);  -- 4

-- 第一個值 (左側截取到逗號前)
SELECT LEFT(@str, CHARINDEX(',', @str, 1) - 1);  -- '462'

-- 去掉第一個值後的剩餘字符串
SELECT SUBSTRING(@str, CHARINDEX(',', @str, 1) + 1, LEN(@str));  -- '464,2'

-- 中間值 (從剩餘字符串再取第一段)
SELECT LEFT(
    SUBSTRING(@str, CHARINDEX(',', @str, 1) + 1, LEN(@str)),
    CHARINDEX(',', SUBSTRING(@str, CHARINDEX(',', @str, 1) + 1, LEN(@str)), 1) - 1
);  -- '464'

-- 最後一個值 (從第二個逗號後截取)
SELECT SUBSTRING(
    SUBSTRING(@str, CHARINDEX(',', @str, 1) + 1, LEN(@str)),
    CHARINDEX(',', SUBSTRING(@str, CHARINDEX(',', @str, 1) + 1, LEN(@str)), 1) + 1,
    LEN(@str)
);  -- '2'

通用解析：WHILE 循環拆分為臨時表/表變量

DECLARE @str NVARCHAR(MAX) = '462,464,2,888,999',
        @delimiter NCHAR(1) = N',',
        @pos INT,
        @val NVARCHAR(100);

DECLARE @Result TABLE (Id INT IDENTITY(1,1), Val NVARCHAR(100));

WHILE LEN(@str) > 0
BEGIN
    SET @pos = CHARINDEX(@delimiter, @str);
    IF @pos = 0
    BEGIN
        SET @val = @str;
        SET @str = N'';
    END
    ELSE
    BEGIN
        SET @val = LEFT(@str, @pos - 1);
        SET @str = SUBSTRING(@str, @pos + 1, LEN(@str));
    END
    INSERT INTO @Result (Val) VALUES (@val);
END

SELECT * FROM @Result;
/*
Id  Val
1   462
2   464
3   2
4   888
5   999
*/

現代化方案：STRING_SPLIT (SQL Server 2016+)

-- 簡單拆分，無順序保證 (2016-2019)
SELECT value FROM STRING_SPLIT('462,464,2', ',');

-- 2022+ 支持 ordinal 保持順序
SELECT value, ordinal FROM STRING_SPLIT('462,464,2', ',') ORDER BY ordinal;

高性能方案：XML/JSON 轉換 (2005+/2016+)

-- XML 法 (2005+)，保持順序
DECLARE @xml XML = CAST('' AS XML);
SELECT r.value('(./text())[1]', 'NVARCHAR(100)') AS Val
FROM @xml.nodes('/r') AS x(r);

-- JSON 法 (2016+)，需嚴格 JSON 格式
SELECT value FROM OPENJSON('["462","464","2"]');

性能對比與選型建議

實戰：將表中分隔符列拆分為多行 (一對多展開)

-- 源表: UserTags(UserId, Tags)  Tags='tag1,tag2,tag3'
-- 目標: 每行一個 Tag

-- 2016+ 最簡潔
SELECT u.UserId, s.value AS Tag
FROM UserTags u
CROSS APPLY STRING_SPLIT(u.Tags, ',') s;

-- 2005-2014 XML 法
SELECT u.UserId, r.value('(./text())[1]', 'NVARCHAR(100)') AS Tag
FROM UserTags u
CROSS APPLY (
    SELECT CAST('' AS XML) AS x
) t
CROSS APPLY t.x.nodes('/r') AS x(r);