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

  

 posted on 2016-09-24 15:12  会飞的金鱼  阅读(2451)  评论(0)    收藏  举报