Skip to main content

SQL Server function to reduce multiple contiguous lines (more than two), into no more than 2 line breaks.

if exists (select * from sys.objects where object_id = object_id(N'[dbo].[CollapseLines]') and type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
    begin
        drop function dbo.CollapseLines;
    end;
go

create function dbo.CollapseLines(
    @str varchar(max)
)
returns varchar(max)
as
begin
    -- ========================================================================
    -- @name CollapseLines
    -- @type User Defined Function
    -- @description Reduce multiple contiguous lines (more than two), into
    --              no more than 2 line breaks.
    -- @str varchar(max) The string containing multiple spaces.
    -- ========================================================================

    declare @nl char(2) = CHAR(13) + CHAR(10);

    while charindex(@nl+@nl+@nl, @str) > 0
        set @str = replace(@str, @nl+@nl+@nl, @nl);
    return @str;
end;
go