|
SQL Server T-SQL User-Defined Function of the WeekWord Wrapping in T-SQLVolume 1 Number 4 December 9, 2002 Visit http://www.NovickSoftware.com for additional material about SQL Server, Visual Basic, ASP, XML, and security. Visit the T-SQL UDF of the Week Archives at: http://www.NovickSoftware.com/UdfOfWeek/UDFofWeekArchive.htm Check out the UDF Frequently Asked Questions at: http://www.novicksoftware.com/UdfOfWeek/UDF_FAQ.htm To ask a UDF related question or to contribute a UDF, send e-mail to udf@NovickSoftware.com Sign up for this newsletter at: http://www.novicksoftware.com/UdfOfWeek/UDFofWeekSignup.htm Word wrapping isn't the kind of task that is done in T-SQL very often. That's a good thing. The SQL Server engine isn't really the right location for formatting functions like wrapping. However, there are times when there isn't a suitable front end that can perform the word wrapping task and it has to be done by the database. For example, when you're generating reports for DBA's that are only run from SQL Query Analyzer. This issues's UDF, udf_TxtN_Wrap, wraps a nvarchar string at a given line length. The list of word separators and the line terminator are parameters to the function. Here's the CREATE FUNCTION script: /------------- Copy From Below this line ----------------------\ CREATE FUNCTION dbo.udf_TxtN_Wrap ( @TxtN2Wrap nvarchar(4000) -- Input text to be wrapped , @nMaxLineLen int -- # Characters allowed on a line. , @Separators nvarchar(255) = ' ' -- List of characters -- that separate words @TxtN2Wrap. Space is -- assumed to be a separator, even if it's not -- included in this parameter. , @LineTerminator nvarchar(255) -- Char used to start a -- new line. If NULL, NCHAR(10) is used. ) RETURNS nvarchar(4000) -- The result string wrapped. /* * This function word wraps a nvarchar string at a given * length. There can be several word separating characters. * Space is always added as a separator. * * Attribution: loosely based on a wrapping function in * VBScript from ASP101.com * * Common Usage: select dbo.udf_TxtN_Wrap (REPLICATE ('12345 ', 200) , 58, N' ', NULL) as [Wrapped Text] select dbo.udf_TxtN_Wrap ('123457890', 4, N' ', NULL) as [Wrapped at 4 with World longer than line] * © Copyright 2002 Andrew Novick http://www.NovickSoftware.com * You may use this function in any of your SQL Server databases * including databases that you sell, so long as they contain * other unrelated database objects. You may not publish this * UDF either in print or electronically. ****************************************************************/ AS BEGIN DECLARE @sOutput nvarchar(4000) -- working var for the result DECLARE @nInputLen int -- Length of input DECLARE @nCurPos int -- Current Position DECLARE @nCurLineStart int -- current line starts DECLARE @nPosOfLastSeparator int -- Last Separator seen -- Save the length of the input SET @nInputLen = Len(@TxtN2Wrap) -- Start both character pointers at the beginning SELECT @nCurPos = 1 , @nCurLineStart = 1 , @nPosOfLastSeparator = 0 SELECT @sOutput = '' -- empty string, not null -- Check the parameters form some of our requirements IF @LineTerminator is NULL SET @LineTerminator = NCHAR(10) -- Cannot use this expression as a Default IF CHARINDEX (N' ', @Separators) = 0 -- Space is SET @Separators = N' ' + @Separators -- Required -- Loop through all characters of the input WHILE @nCurPos < @nInputLen BEGIN -- Make note of the last separator for use later. If CHARINDEX(SUBSTRING(@TxtN2Wrap, @nCurPos, 1) , @Separators, 1) > 0 BEGIN SET @nPosOfLastSeparator = @nCurPos End -- If -- Once we have enough for a line, go back to -- the last separator we saw and end the line there. IF @nCurPos >= @nCurLineStart + @nMaxLineLen - 1 BEGIN if @nPosOfLastSeparator = 0 BEGIN -- Cases where a word is longer than the line length -- Append the new to the result Set @sOutput = @sOutput + SUBSTRING(@TxtN2Wrap, @nCurLineStart, @nMaxLineLen) + @LineTerminator SET @nCurLineStart = @nCurLineStart + @nMaxLineLen END ELSE BEGIN -- Append this new line to the result SET @sOutput = @sOutput + RTRIM(LTRIM( SUBSTRING(@TxtN2Wrap , @nCurLineStart , @nPosOfLastSeparator - @nCurLineStart + 1) )) + @LineTerminator -- Reset the next line's starting point to the -- point used for the last one's end + 1. SELECT @nCurLineStart = @nPosOfLastSeparator + 1 , @nPosOfLastSeparator = 0 -- Don't have one now. END -- Remove any leading separators from the new line. While CHARINDEX(SUBSTRING(@TxtN2Wrap , @nCurLineStart, 1) , @Separators, 1) > 0 BEGIN SELECT @nCurLineStart = @nCurLineStart + 1 , @nCurPos = @nCurLineStart + 1 END -- While END -- IF -- Increment our current position. SET @nCurPos = @nCurPos + 1 END -- While -- If the loop ends before we add all the text, add it now. SET @sOutput = @sOutput + RTRIM(LTrim(SUBSTRING(@TxtN2Wrap , @nCurLineStart , @nInputLen - @nCurLineStart+1) )) RETURN @sOutput End -- Function GRANT EXECUTE ON [dbo].[udf_TxtN_Wrap] TO [Public] GO \------------ Stop copying above this line --------------------/ Before you run udf_TxtN_Wrap, you should be aware of an few important features of the SQL Query Analyzer (QA). QA limits the length of every output column to a length set in one of it's options. Use the menu Tools->Options and set the "Maximum characters per column:" field to 8192. That's the largest's number it will allow. Also, wrapped text doesn't show up if you send your results to grid. It's only works when the results go to "text" or to a file. Use the menu Query->Results in Text to set textual output. Now that you've created the function and set the size of an output column, let's test it out: /------------- Copy From Below this line ----------------------\ select dbo.udf_TxtN_Wrap (REPLICATE(N'123456789 ', 20) , 60, N' ', NULL) as [Wrapped Nubmers at 60] GO \------------ Stop copying above this line --------------------/ (Results) Wrapped Nubmers at 60 ---------------------------------------------------------------- 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 Of course, you'll usually be using it on some type of string column and not on a bunch of numbers, but you get the idea. udf_TxtN_Wrap comes in handy when you have no choice but to wrap text in Query Analyzer. +--------------------------------------------------------------+ Do you have a great UDF that you'd like to share? Or maybe you have a T-SQL problem that you think could be solved by a UDF but you don't know how? Please send them to: UDF@NovickSoftware.com and they might be published in this newsletter. I try and respond to every request that I get. Thanks, Andrew Novick +--------------------------------------------------------------+ This newsletter is published by Novick Software http://NovickSoftware.com Copyright (c) 2002 Novick Software. All rights reserved.
|
|
