十五、SQLSERVER String2Table

将一个字符串转换成一个表变量

1、创建函数

ALTER FUNCTION [dbo].[fn_String2Table]
(
    @String VARCHAR(8000),          --要转换的字符串。
    @Separator VARCHAR(10) = ','   --分隔符。
)
RETURNS @Table TABLE([ID] BIGINT)
 
AS  
BEGIN
    SET @String = @String + @Separator
    WHILE CHARINDEX(@Separator, @String) > 0
    BEGIN
        INSERT INTO @Table ([ID])
        VALUES(SUBSTRING(@String, 1, CHARINDEX(@Separator, @String) - 1))
    
        SELECT @String = SUBSTRING(@String, CHARINDEX(@Separator, @String) + 1, LEN(@String) - CHARINDEX(@Separator, @String))
    END
    RETURN
END

 

2、调用

DECLARE @TempTable TABLE([ID] INT)
INSERT INTO @TempTable ([ID]) SELECT [ID] FROM fn_String2Table(@Ids, ',')