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.