SQL Server 字符串转数组（拆分）

1、动态SQL法

declare @string varchar(100), @sql varchar(1000)
set @string = '1,2,3,4,5,6,7,8,9,10'
set @sql = 'select col=''' + replace(@string, ',', ''' union all select ''') + ''''
PRINT @sql
exec (@sql)

2、循环截取法

CREATE FUNCTION F_SplitSTR(
    @string varchar(8000), --待分拆的字符串
    @symbol varchar(10)     --数据分隔符
) RETURNS @table TABLE(col varchar(100))
AS
    BEGIN
        DECLARE @splitlen int
        SET @splitlen = LEN(@symbol) - 1
        WHILE CHARINDEX(@symbol, @string) > 0
            BEGIN
                INSERT @table VALUES (LEFT(@string, CHARINDEX(@symbol, @string) - 1))
                SET @string = STUFF(@string, 1, CHARINDEX(@symbol, @string) + @splitlen, '')
            END
        INSERT @table VALUES (@string)
        RETURN
    END
GO

SELECT * FROM F_SplitSTR('1,2,3,4,5,6', ',')