SQL 如何去除重复的字符串

最新推荐文章于 2024-08-02 21:18:55 发布

Karl_zhujt

最新推荐文章于 2024-08-02 21:18:55 发布

阅读量5.4k

点赞数

分类专栏： SSMS 文章标签： sql function

SSMS 专栏收录该内容

31 篇文章 1 订阅

订阅专栏

方案一：

declare @ret varchar(8000)
select @ret = 'APR-11,APR12,APR06,APR-11,APR12,APR06'

select distinct name=substring(@ret,number,charindex(',',@ret+',',number)-number)
from master..spt_values
where number<=len(@ret) and type='P' 
and substring(','+@ret,number,1)=','
--------------------------
APR06
APR-11
APR12

方案二：

create function GetDistinct(@str varchar(1000))
returns varchar(1000)
as
begin
    declare @temp varchar(1000)
    while(charindex(','+substring(@str,1,charindex(',',@str)-1)+',',','+isnull(@temp,'')+',')=0)
    begin
        set @temp=isnull(@temp+',','')+substring(@str,1,charindex(',',@str)-1)
        set @str=substring(@str,charindex(',',@str)+1,len(@str))
    end
    return @temp
end
go

select dbo.getdistinct('APR-11,APR12,APR06,APR-11,APR12,APR06')
--结果：
--------------------------
APR-11,APR12,APR06

方案三：
 
declare @ret varchar(8000)
select @ret = 'APR-11,APR12,APR06,APR-11,APR12,APR06'

select @ret = 'select '''+replace(@ret,',',''' union select ''')+''''

exec(@ret)
----------------------
APR06
APR-11
APR12


方案四：
declare @str varchar(8000)
declare @ret varchar(8000),@return varchar(8000)

select @str = 'APR-11,APR12,APR06,APR-11,APR12,APR06'

select @str = @str+','

while charindex(',',@str) > 0
begin
    select @ret = substring(@str,1,charindex(',',@str)-1)
    
    select @return = isnull(@return+',','')+@ret

    select @str = replace(@str,@ret+',','')
end 

select @return
---------------------------
APR-11,APR12,APR06