字符串'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