Replace consecutive duplicate letters with a single letter

Question

How do I create a function in SQL Server 2017 that identifies when a string contains duplicate consecutive letters (a-z) and replaces those duplicate letters with a single instance of that letter?

Here are some examples of what should happen:

CompanyAAABCD -> CompanyABCD
CommpanyABYTTT -> CompanyABYT
Company11111 -> Company11111

Answer 1

alter function fn_RemoveDuplicateChar(@name varchar(200))
RETURNS VARCHAR(200) 
as
begin 
    declare @strPosition int=1;
    declare @strlen int=0;
    declare @finalstr varchar(200)='';
    declare @str varchar(200)='';
    declare @fstr varchar(200)=''; 
    select @strlen = (select len(@name))

    while @strPosition<=@strlen
    begin
        select @fstr = SUBSTRING(@name, @strPosition, 1)
        select @str  = SUBSTRING(@finalstr, len(@finalstr), 1) 
        If @fstr <> @str or ( ISNUMERIC(@fstr)=1 and ISNUMERIC(@str)=1)
        set @finalstr = @finalstr + @fstr
        set @strPosition =@strPosition+1    
    end
    return (select @finalstr)
end
go
select dbo.fn_RemoveDuplicateChar('CompanyAAABCD')
select dbo.fn_RemoveDuplicateChar('CommpanyABYTTT')
select dbo.fn_RemoveDuplicateChar('Company11111')