CREATE FUNCTION [dbo].[ConvertListToColumn] (@list nvarchar(MAX))RETURNS @tbl TABLE (number int NOT NULL) AS
BEGIN
DECLARE @pos int,@nextpos int,@valuelen intSELECT @pos = 0, @nextpos = 1WHILE @nextpos > 0
BEGIN
SELECT @nextpos = charindex(',', @list, @pos + 1)SELECT @valuelen = CASE WHEN @nextpos > 0
THEN @nextpos
ELSE len(@list) + 1
END - @pos - 1
INSERT @tbl (number)
VALUES (convert(int, substring(@list, @pos + 1, @valuelen)))
SELECT @pos = @nextpos
END
RETURN
END
No comments:
Post a Comment