Mline UDF - Return specific line
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.
$SAMPLECODE$
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 -- 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)
Comments