Sql Server中常用的6个自定义函数分享
转自:http://www.jb51.net/article/56691.htm
1 IF OBJECT_ID('DBO.DISTINCT_STR') IS NOT NULL 2 DROP FUNCTION DBO.DISTINCT_STR 3 GO 4 --此函数操作形如["a", "b", 1,2,3,1,"a"]的数组,去重后,["b",1,2,3] 5 --@S(原有参数), @SPLIT(分隔符), @NEW_STR(新添加字符串) 6 CREATE FUNCTION DISTINCT_STR(@S NVARCHAR(300),@SPLIT VARCHAR(50),@NEW_STR VARCHAR(300)) 7 RETURNS VARCHAR(300) 8 AS 9 BEGIN 10 DECLARE @NEW VARCHAR(200),@INDEX INT,@TEMP VARCHAR(200) 11 12 IF CHARINDEX(']',@S)>0 and CHARINDEX(']', @S) > 0 13 SET @S=SUBSTRING(@S,charindex('[',@S)+1,LEN(@S)-2) + @SPLIT + @NEW_STR 14 ELSE 15 SET @S=@NEW_STR 16 17 IF LEFT(@S,1)<>@SPLIT 18 SET @S = @SPLIT+@S 19 IF RIGHT(@S,1)<>@SPLIT 20 SET @S = @S+@SPLIT 21 WHILE CHARINDEX(@SPLIT,@S)>0 AND LEN(@S)<>1 22 BEGIN 23 SET @INDEX = CHARINDEX(@SPLIT,@S) 24 SET @TEMP = LEFT(@S,CHARINDEX(@SPLIT,@S,@INDEX+LEN(@SPLIT))) 25 IF @NEW IS NULL 26 SET @NEW = ISNULL(@NEW,'')+@TEMP 27 ELSE 28 SET @NEW = ISNULL(@NEW,'')+REPLACE(@TEMP,@SPLIT,'')+@SPLIT 29 WHILE CHARINDEX(@TEMP,@S)>0 30 BEGIN 31 SET @S=STUFF(@S,CHARINDEX(@TEMP,@S)+LEN(@SPLIT),CHARINDEX(@SPLIT,@S,CHARINDEX(@TEMP,@S)+LEN(@SPLIT))-CHARINDEX(@TEMP,@S),'') 32 END 33 END 34 RETURN '['+RIGHT(LEFT(@NEW,LEN(@NEW)-1),LEN(LEFT(@NEW,LEN(@NEW)-1))-1)+']' 35 END 36 GO
