sql server分割字符串

在 SQL Server 中，可以使用以下方法来分割字符串：

方法1：使用XML

DECLARE @str varchar(100) = 'a,b,c,d'
SELECT t.value('.', 'VARCHAR(20)') AS value
FROM (SELECT CAST('<t>' + REPLACE(@str, ',', '</t><t>') + '</t>' AS XML) AS x) AS y
CROSS APPLY x.nodes('/t') AS z(t)

方法2：使用字符串函数

DECLARE @str varchar(100) = 'a,b,c,d'
DECLARE @delimiter char(1) = ','
DECLARE @pos int, @nextpos int, @val varchar(20)
SELECT @pos = 0, @nextpos = 1
WHILE @nextpos > 0
BEGIN
    SELECT @nextpos = CHARINDEX(@delimiter, @str, @pos + 1)
    IF @nextpos > 0
        SELECT @val = SUBSTRING(@str, @pos + 1, @nextpos - @pos - 1)
    ELSE
        SELECT @val = SUBSTRING(@str, @pos + 1, LEN(@str))
    SELECT @pos = @nextpos
    SELECT @val AS value
END