Count CRLF Pairs in a String

A scalar function that counts how many Windows-style carriage return / line feed (CRLF) pairs exist inside a string.

Published Mar 4, 2010

Sometimes you need to know how many lines are in a block of text before you do anything with it - for example when you are preparing data for a fixed-width report or checking whether a message has grown past a limit. This little scalar function walks the string and counts every occurrence of the two-character Windows line ending (CHAR(13) + CHAR(10)).

Example: SELECT dbo.CountCRLFs('Line one\r\nLine two\r\nLine three') -- returns 2

CREATE FUNCTION CountCRLFs
(
	@String VarChar(8000)
)
RETURNS INT
AS
BEGIN
	DECLARE @Count INT
	DECLARE @LastIndex INT
	DECLARE @CRLF	VarChar(2)

	SET @CRLF = CHAR(13) + CHAR(10)


	SET @LastIndex = CharIndex(@CRLF, @String)

	WHILE(@LastIndex > 0)
	BEGIN
		SET @LastIndex = CharIndex(@CRLF, @String, @LastIndex + 1)
		SET @Count = IsNull(@Count, 0) + 1
	END

	RETURN @Count
END
GO