question

Shailendra avatar image
Shailendra asked

Column width formating in text output

Hi SQL experts,

Using sqlcmd I am running input.txt which is having some simple select statements and I am creating output.txt with output of select statements. I see that many of column witdths in output e.g loginname have width between 80-90 whereas the max loginname spread as much as 25 charcters only. Similarly I have many columns with such formating issue.

I want to format the output so that unnecessary spaces are excluded and column width will be best suitable without truncating any data.

In oracle they have something like below:


column format a<##>

Replace columnname with the name of the column you want to change. The "a" after format means alphanumeric. Replace ## with the width you want.


Do we have similar command in SQL. I badly need this for some report generation.

Thanks, Shailendra

query
10 |1200

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

KenJ avatar image
KenJ answered

If you CAST your columns to a narrower data type, their output columns will be narrower. The following would limit the loginname column to 25 characters:

SELECT 
    CAST(loginname varchar(25)) as loginname
FROM ...
10 |1200

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

TimothyAWiseman avatar image
TimothyAWiseman answered

KenJ is quite correct.

Remember though that casting it to varchar(x) will truncate all characters beyond x. If you are not certain what the longest string in the column is you can use the len function to get it. Something like.

select
  max(len(colname))
from
  tablename

Also, if you want to ensure the output is precisely a certain number of characters long, you cna cast it as char instead of varchar. This can be useful if you want to create fixed width lines or make sure there is a certain amount of space between elements when concatenating. For instance:

select
  cast(col1 as char(10)) + cast(col2 as char(20)) as fixedWidthColumn
from
 tablename
10 |1200

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

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.