Word wrapping with T-SQL

If there isn't a suitable front end that can perform the word wrapping task, it can done by SQL Server. Here's how.

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

Dig deeper on SQL Server and .NET development

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchCloudComputing

SearchSoftwareQuality

SearchSOA

TheServerSide

SearchCloudApplications

Close