SQL Server 字符串去重复

     字符串'a,a,bb,b,bb,c,d,d'，要得到的结果'a,bb,b,c,d'。

     1）用'union select '替换掉','，然后执行动态sql

declare @str varchar(100)='a,a,bb,b,bb,c,d,d'
declare @sql varchar(200)=''
set @sql=' select '''+replace(@str,',',''' union select ''')+''''
--print @sql
exec(@sql)

　  结果不是最终的，只要合并一下即可。

　

    2）用master..spt_values表关联

declare @str varchar(100)='a,a,bb,b,bb,c,d,d'
select distinct name=SUBSTRING(@str,number,CHARINDEX(',',@str+',',number)-number)
from master..spt_values where number<=LEN(@str) and type='p' and SUBSTRING(','+@str,number,1)=','

　  结果与上面的类似。　

    3）循环截取字符串

declare @str varchar(100)='a,a,bb,b,bb,c,d,d'
declare @tmp varchar(100)=''
declare @return varchar(100)
set @str=@str+','
while(charindex(',',@str)>0)
begin
   set @tmp=substring(@str,1,charindex(',',@str)-1)
   set @return=isnull(@return+',','')+@tmp
   set @str=REPLACE(@str,@tmp+',','')
end
select @return

　 结果：a,bb,b,c,d

   4）XML

declare @str varchar(max)='a,a,bb,b,bb,c,d,d'
declare @xml xml=''
set @xml=CAST('<root><col val="'+replace(@str,',','" /><col val="')+'"></col></root>' as xml)
--select @xml
select stuff((
select distinct ','+t.c.value('@val','varchar(max)')  from @xml.nodes('/root/col') t(c) for xml path('')
),1,1,'')

　　结果与上面一样

利用上面的方法写成字符串去重的函数，因为在函数无法执行exec()，因此无法创建函数。　　

--根据方法二，改编成的函数
create function func_GetDistinct_2
(
@str varchar(8000),
@splitChar char(1)
)
returns varchar(8000)
as 
begin
declare @return varchar(8000)=''
select @return=@return+@splitChar+name from (
select  distinct name=substring(@str,number,charindex(@splitChar,@str+@splitChar,number)-number)
from master..spt_values
where number<=len(@str) and type='p' and substring(@splitChar+@str,number,1)=@splitChar
)as a
return stuff(@return,1,1,'')
end
go

--根据方法三，改编成的函数
create function func_GetDistinct_3
(
@str varchar(8000),
@splitChar char(1)
)
returns varchar(8000)
as
begin
declare @temp varchar(4000)=''
declare @return varchar(8000)=''
set @str=@str+@splitChar
while(charindex(@splitChar,@str,1)>0)
begin
    set @temp=substring(@str,1,charindex(@splitChar,@str,1)-1)
    set @return=isnull(@return+@splitChar,'')+@temp
    set @str=replace(@str,@temp+@splitChar,'')
end
   return stuff(@return,1,1,'')
end
go

--根据方法四，改编成的函数
create function func_GetDistinct_4
(
@str varchar(8000),
@splitChar char(1)
)
returns varchar(8000)
as
begin
  declare @xml xml=''
  declare @return varchar(8000)=''
  set @str=@str
  set @xml=cast('<root><col val="'+replace(@str,@splitChar,'" /><col val="')+'"></col></root>' as xml)
  set @return=STUFF(
   (select distinct @splitChar+t.c.value('@val','varchar(255)')  from @xml.nodes('/root/col') as t(c) for xml path(''))
,1,1,'')
  return @return
end