--创建函数
create function [dbo].[m_delrepeatsplit]
(
@str varchar(2000),
@split nvarchar(200)
)
returns nvarchar(2000)
as
begin
--begin declare
declare @count int,@i int,@isnull int
declare @newchar nvarchar(200),@nn nvarchar(300)
set @count=len(@str);set @i=1;set @isnull=1;set @nn=‘‘;
--end declare
--begin while
while @i<@count+1
begin
set @newchar=substring(@str,@i,1)
if(@isnull=1)
begin
set @nn=@nn+@newchar;
if(@newchar=@split)
begin
set @isnull=0;
end
else
begin
set @isnull=1;
end
end
else
begin
if(@newchar=@split)
begin
set @isnull=0;
end
else
begin
set @nn=@nn+@newchar;
set @isnull=1;
end
end
set @i=@i+1;
end
--end while
return @nn
end
--2、测试示例
declare @str nvarchar(200)
set @str=‘1 2 3 4 555 6 7 7‘;
declare @split nvarchar(200)
set @split=‘ ‘;
select dbo.m_delrepeatsplit(@str,@split) as newchar
--3、运行结果
/*
newchar
------------------
1 2 3 4 555 6 7 7
*/
原文：http://www.cnblogs.com/accumulater/p/6244447.html