SqlServer2008实例57修改用户定义函数

  使用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)

 

©️2020 CSDN 皮肤主题: 编程工作室 设计师:CSDN官方博客 返回首页