create table t(id int,tag varchar(100))
insert into t select 1,'飞机,大炮,轮船,火箭,火箭,飞机'
insert into t select 2,',,,,飞机,楼房,,,,,,'
insert into t select 3,',,,,,,,,,,'
insert into t select 4,',飞机,,火箭,,大炮'
insert into t select 5,'飞机,飞机,飞机,,'
go
create function f_str(@tag varchar(100))
returns varchar(100)
as
begin
declare @str varchar(100)
set @str=','
set @tag=@tag+','
while(charindex(',',@tag))>0
begin
if (charindex(left(@tag,charindex(',',@tag)),@str)=0)
set @str=@str+left(@tag,charindex(',',@tag))
set @tag=stuff(@tag,1,charindex(',',@tag),'')
end
set @str=case when len(@str)>1 then substring(@str,2,len(@str)-2) else '' end
return @str
end
go
select id,dbo.f_str(tag) as tag from t
go
/*
id tag
----------- -------------------------------
1 飞机,大炮,轮船,火箭
2 飞机,楼房
3
4 飞机,火箭,大炮
5 飞机
*/
drop function f_str
drop table t
go
