question

A1059 avatar image
A1059 asked

SSMS Text output numeric column width

Hello, I am new to SQL Server Management Studio (2005). I use it with text output for quick analysis of our data. The statement SELECT 123.123456 A, CAST(123.123456 AS DECIMAL(6, 2)) B, CONVERT(NUMERIC(8, 4), 123.123456) C returns the 3 columns, each with 49 characters width, no matter what the cast or convert arguments are. This means data easily falls out of screen and requires a lot of horizontal scrolling. Is it possible to control the width of the numerical columns in SSMS (with text output)? It used to work well with the "Query Analyzer". What I would like to get from the previous statement is: A B C ....................................... ...... ........ 123.123456 123.12 123.1235 (hyphens don't show well in the preview). Thanks!!!
output
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
KenJ avatar image
KenJ answered
If grid output doesn't suit you and you're formatting specifically for the text layout in SSMS, you can re-cast your columns as fixed width character columns: SELECT CAST(123.123456 AS CHAR(10)) A, CAST(CAST(123.123456 AS DECIMAL(6, 2)) AS CHAR(10)) B, CAST(CONVERT(NUMERIC(8, 4), 123.123456) AS CHAR(10)) C A B C ---------- ---------- ---------- 123.123456 123.12 123.1235 (1 row(s) affected) (Query output formatted as code in the question editor to preserve hyphens and spaces)
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thank you for your reply Ken! I was just wondering if I could avoid re-casting.
0 Likes 0 ·
if you don't mind your column headers not lining up with the data, you can go to Tools -> Options... -> Query Results -> SQL Server -> Results to Text and change the output format from "Column aligned" to one of the delimited options. This will display the result along with the delimiter and remove the padding that comes with column alignment. This dialog also lets you leave column headers out, right align numeric results, and limit the maximum number of characters displayed in each column (with a 30 character minimum)
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.