Home » Pages » SQL » Microsoft SQL Server

Mline UDF - Return specific line

By Sergey - Posted on November 23rd, 2007
Tagged:

The ufn_Mlines() UDF for SQL Server is similar to the VFP Mline() function but w/o third parameter.
It's more an example than finished UDF. I didn't do much testing on it.

This is sample code. Add error handling and adjust to your requirements as necessary.

-- ufn_Mline ---
IF EXISTS (SELECT * FROM   sysobjects WHERE  name = N'ufn_Mline')
    DROP FUNCTION ufn_Mline
GO
CREATE FUNCTION ufn_Mline 
	(	@STR VARCHAR(8000),
		@LineNumber INT)
RETURNS VARCHAR(8000)
AS
/* Returns a specific line from a string. Each line is separated by @separator 
 
 Parameters:
    @str  - string passed in
    @LineNumber - Line number to return
 Returns either a line requested or empty string if @LineNumber exceeds # of lines in @str
*/
BEGIN
	DECLARE @separator VARCHAR(16), @Line VARCHAR(8000), @pos INT, @i INT
	-- Lines are separated by CR (carrage return) only
	SET @separator = CHAR(13)
	SET @Line = ''
	SET @i = 1
	WHILE DATALENGTH(@STR) > 0 BEGIN
		SET @pos = CHARINDEX(@separator, @STR)
		IF @pos = 0 
			SET @pos = DATALENGTH(@STR)+1
		IF @i = @LineNumber BEGIN							
			SET @Line = LEFT(@STR, @pos -1)
			BREAK
		END	
		SET @STR = SUBSTRING(@STR, @pos + DATALENGTH(@separator), 8000)
		SET @i = @i + 1
	END
 
	RETURN @Line
 
END
 
GO
 
-- Sample of using ufn_Mline UDF
SELECT dbo.ufn_Mline('The quick brown fox' + CHAR(13) + 'jumps over the lazy dog',1)
SELECT dbo.ufn_Mline('The quick brown fox' + CHAR(13) + 'jumps over the lazy dog',2)
SELECT dbo.ufn_Mline('The quick brown fox' + CHAR(13) + 'jumps over the lazy dog',3)