用SQL将连续相同的字符压缩成一个字符

/*    reduce the consecutive characters alike into single one    WinXP, SQL Server 2K*/if object_id('fn_unicate') is not null    drop function fn_unicategocreate function dbo.fn_unicate(    @string nvarchar(200), -- the string to be operated on    @target nvarchar(10)) -- the target characters to be compressed (null and empty string meas compress all)    returns nvarchar(200)asbegindeclare @t table(c nvarchar(100))declare @i int, @value nvarchar(100), @ret nvarchar(200)select @i = 2, @value = left(@string, 1), @ret = ''while @i <= len(@string)begin    if right(@value, 1) <> substring(@string, @i, 1)    begin        insert @t select @value        select @value = ''    end    select @value = @value + substring(@string, @i, 1), @i = @i + 1endselect @ret = @ret +    case when isnull(@target, '') = '' then left(c,1)        when charindex(left(c,1), @target) > 0 then left(c, 1)        else c end    from @treturn @retendgodeclare @s nvarchar(200)set @s = 'aaabcckkkkkaaaaaairrrrr'print 'original string = ' + @sprint dbo.fn_unicate(@s, '')print dbo.fn_unicate(@s, null)print dbo.fn_unicate(@s, 'k') -- compress 'k'print dbo.fn_unicate(@s, 'ar') -- compress 'a' and 'r'print dbo.fn_unicate('', '') + 'test'print dbo.fn_unicate(null, '') + 'test2'godrop function fn_unicatego