Remove duplicate side-by-side characters from a string
New approach using numbers table
CREATE FUNCTION dbo.fnRemoveDupesI (@String VARCHAR(8000))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @result VARCHAR(8000) = '';
;WITH DataOrder
AS
(
SELECT ID, Data
,ROW_NUMBER() OVER (PARTITION BY Data ORDER BY ID) AS RowNum
FROM (SELECT SUBSTRING(@String, nbr, 1), nbr
FROM Nums
WHERE nbr <= LEN(@String)
) D(data, ID)
)
SELECT @result = @result + Data
FROM (SELECT ID, Data
,DENSE_RANK() OVER (ORDER BY ID - RowNum) As [Rank]
FROM DataOrder
)D
GROUP BY Data, [Rank]
ORDER BY MIN(ID)
RETURN @result
END;