使用ALTER FUNCTION命令能修改函数,
USE AdventureWorks
GO
--创建一个把字符数组作为结果集返回UDF
ALTER FUNCTION dbo.udf_ParseArray
(@StringArray varchar(max),
@Delimiter char(1),
@MinRowSelect int,
@MaxRowSelect int
)
RETURNS @StringArrayTable Table (RowNum int IDENTITY(1,1),val varchar(50))
AS
BEGIN
DECLARE @Delimiter_position int
IF RIGHT(@StringArray,1) !=@Delimiter
SET @StringArray=@StringArray + @Delimiter
WHILE CHARINDEX(@Delimiter,@StringArray)<>0
BEGIN
SELECT @Delimiter_position=CHARINDEX(@Delimiter,@StringArray)
INSERT @StringArrayTable
VALUES (left(@StringArray,@Delimiter_position-1))
SELECT @StringArray=STUFF(@StringArray,1,@Delimiter_Position,'')
END
DELETE @StringArrayTable
WHERE RowNum<@MinRowSelect OR RowNum>@MaxRowSelect
RETURN
END
现在用它分离逗号分隔的数组
SELECT RowNum,Val
FROM dbo.udf_ParseArray('A,B,C,D,E,F,G',',',3,5)