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 DBAs that are only run from SQL Query Analyzer.
Here is a UDF, called udf_TxtN_Wrap, that 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:
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
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 its 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:
select dbo.udf_TxtN_Wrap (REPLICATE(N'123456789 ', 20)
, 60, N' ', NULL) as [Wrapped Nubmers at 60]
GO
Here are the 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.
About the Author
Andrew Novick is a 22-year industry veteran and principal of the New England based consulting company, Novick Software. He has recently co-authored SQL Server 2000 XML Distilled, which was published by Curlingstone in October 2002. As a long time project manager, consultant, and programmer Andy has designed and built applications for the financial services, retail, transportation, telecommunications, and real estate industries. He specializes in building systems using the Microsoft tools, usually SQL Server, Visual Basic, ASP, XML, and more recently .Net. He can be reached at anovick@NovickSoftware.com.
For More Information
- Feedback: E-mail the editor with your thoughts about this tip.
- More tips: Hundreds of free SQL Server tips and scripts.
- Tip contest: Have a SQL Server tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
- Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
- Forums: Ask your technical SQL Server questions--or help out your peers by answering them--in our active forums.
- Best Web Links: SQL Server tips, tutorials, and scripts from around the Web.
This was first published in December 2002