T-SQL: replace occurrences of two or more characters in a row with a single new character Christian Donner, May 31, 2013May 31, 2013 Here is a T-SQL UDF that replaces occurrences of two or more characters in a row with a single new character, a delimiter. CREATE FUNCTION dbo.collapsecharsequence ( @fulldata nvarchar(max), @char nchar(1), @delim nchar(1) ) RETURNS nvarchar(max) AS BEGIN DECLARE @i int = 1; WHILE @i < LEN(@fulldata)-1 BEGIN IF (SUBSTRING(@fulldata, @i, 1) IN (@char, @delim) AND SUBSTRING(@fulldata, 1+@i, 1) = @char) SET @fulldata = STUFF(@fulldata, @i, 2, @delim); ELSE SET @i = @i + 1; END RETURN @fulldata; END I use this function in a label data parsing application for collapsing spaces found in data fields into a single delimiter. For example: select dbo.collapsecharsequence('null eins zwei drei|vier', ' ', '|'); returns this: null|eins|zwei drei|vier I consider a single space as part of the data value, but multiple spaces indicate that there are actually two separate data elements that I want to treat individually. Related Posts:Enphase Envoy Local AccessTyreWiz not working after battery changeThe Great Cat Litter Poop OffAmazon threatens customer of 26 yearsSUTAB Scam? Software Engineering SQL Server delimiterfunctionreplacestringT-SQL