Try the below code to remove duplicate characters from the string.
DECLARE @Str VarChar(8000) = '-----------:: Chand Dakhara ::-----------'
DECLARE @DuplicateChar VarChar(1) = '-'
DECLARE @SearchChar VARCHAR(2)
SET @SearchChar = @DuplicateChar + @DuplicateChar
SELECT @Str AS 'Before Replace'
BEGIN
WHILE CHARINDEX(@SearchChar, @Str) > 0
BEGIN
SET @Str = REPLACE(@Str, @SearchChar,@DuplicateChar)
END
SELECT @Str AS 'After Replace'
END
Here I’m setting the addition of the @DuplicateChar string into @SearchChar. Logic is very simple. Through the loop search @SearchChar into the string and if found then replace it with the @DuplicateChar.
