MICROSOFT SQL SERVER
Word wrapping with T-SQL
Andrew Novick
12.15.2002
Rating: --- (out of 5)
|
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:
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:
Here are the results:
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 ...
To continue reading for free, register below or login
To read more you must become a member of SearchWinDevelopment.com
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.
|
|
|
|
|
|
