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.
