Sql Server中常用的6个自定义函数分享

转自:http://www.jb51.net/article/56691.htm

IF OBJECT_ID('DBO.DISTINCT_STR') IS NOT NULL
DROP FUNCTION DBO.DISTINCT_STR
GO
--此函数操作形如["a", "b", 1,2,3,1,"a"]的数组,去重后,["b",1,2,3]
--@S(原有参数), @SPLIT(分隔符), @NEW_STR(新添加字符串)
CREATE FUNCTION DISTINCT_STR(@S NVARCHAR(300),@SPLIT VARCHAR(50),@NEW_STR VARCHAR(300))
RETURNS VARCHAR(300)
AS
BEGIN
DECLARE @NEW VARCHAR(200),@INDEX INT,@TEMP VARCHAR(200)

IF CHARINDEX(']',@S)>0 and CHARINDEX(']', @S) > 0
SET @S=SUBSTRING(@S,charindex('[',@S)+1,LEN(@S)-2) + @SPLIT + @NEW_STR
ELSE
SET @S=@NEW_STR

IF LEFT(@S,1)<>@SPLIT
SET @S = @SPLIT+@S
IF RIGHT(@S,1)<>@SPLIT
SET @S = @S+@SPLIT
WHILE CHARINDEX(@SPLIT,@S)>0 AND LEN(@S)<>1
BEGIN
SET @INDEX = CHARINDEX(@SPLIT,@S)
SET @TEMP = LEFT(@S,CHARINDEX(@SPLIT,@S,@INDEX+LEN(@SPLIT)))
IF @NEW IS NULL
SET @NEW = ISNULL(@NEW,'')+@TEMP
ELSE
SET @NEW = ISNULL(@NEW,'')+REPLACE(@TEMP,@SPLIT,'')+@SPLIT
WHILE CHARINDEX(@TEMP,@S)>0
BEGIN
SET @S=STUFF(@S,CHARINDEX(@TEMP,@S)+LEN(@SPLIT),CHARINDEX(@SPLIT,@S,CHARINDEX(@TEMP,@S)+LEN(@SPLIT))-CHARINDEX(@TEMP,@S),'')
END
END
RETURN '['+RIGHT(LEFT(@NEW,LEN(@NEW)-1),LEN(LEFT(@NEW,LEN(@NEW)-1))-1)+']'
END
GO