Remove Repeating Characters From a String

Below is a solution to remove a random number of repeating characters from a string. I created a string with a randomly repeating letter q. Some real world needs are for removing repeating spaces. I did not create this solution but wanted to document and explain it in this BLOG for the benefit of visitors.This is a single-pass solution for multiple and variable repeating characters within the string.


--string with repeating characters to remove
DECLARE @String VARCHAR(1000) = 'My qqquery was bad qquality and returned data with randomly repeating qq letters and so we needed a way to reduce multiple qqqq''s to a single qqqqqqqqqqq.';

--repeating character to remove
DECLARE @TrimCharacter VARCHAR(1) = 'q';

SELECT
@String AS OriginalString
,REPLACE(@String,@TrimCharacter,'<>') AS FirstReplace
,REPLACE(REPLACE(@String,@TrimCharacter,'<>'),'><','') AS SecondReplace
,REPLACE(REPLACE(REPLACE(@String,@TrimCharacter,'<>'),'><',''),'<>',@TrimCharacter) AS FinalResult;

ORIGINAL STRING – Green highlights of all 'q' letters

My qqquery was bad qquality and returned data with randomly repeating qq letters and so we needed a way to reduce multiple qqqq's to a single qqqqqqqqqqq.

FIRST INNER REPLACE – Replaces each 'q' (green above) with '<>'

My <><><>uery was bad <><>uality and returned data with randomly repeating <><> letters and so we needed a way to reduce multiple <><><><>'s to a single <><><><><><><><><><><>.

SECOND MIDDLE REPLACE – Replaces each '><' (yellow above) with empty string

My <>uery was bad <>uality and returned data with randomly repeating <> letters and so we needed a way to reduce multiple <>'s to a single <>.

THIRD OUTER REPLACE - Replaces each remaining '<>' (aqua above) with a 'q'

My query was bad quality and returned data with randomly repeating q letters and so we needed a way to reduce multiple q's to a single q.

If the string happens to contain either '<' or '>' the solution would need to be modified so as to avoid the conflicts. Instead of '<>' a generic solution can use the unprintable characters: CHAR(17)+CHAR(18)

REPLACE(REPLACE(REPLACE(@String,@TrimCharacter,CHAR(17)+CHAR(18)),CHAR(18)+CHAR(17),''),CHAR(17)+CHAR(18),@TrimCharacter) AS FinalResult