Site icon Donner's Daily Dose of Drama

T-SQL: replace occurrences of two or more characters in a row with a single new character

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.

Exit mobile version