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

/*
    reduce the consecutive characters alike into single one
    WinXP, SQL Server 2K
*/

if object_id('fn_unicate') is not null
    drop function fn_unicate
go

create 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)
as
begin
declare @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 + 1
end

select @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 @t
return @ret
end
go

declare @s nvarchar(200)
set @s = 'aaabcckkkkkaaaaaairrrrr'
print 'original string = ' + @s
print 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'
go

drop function fn_unicate
go