Column width formating in text output

• jshailendra

  SSCrazy

  Points: 2260

  More actions

  January 20, 2010 at 2:55 am

  #229264

  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

• Erik Bitemo

  SSCrazy

  Points: 2062

  More actions

  January 20, 2010 at 10:32 am

  #1106190

  Hi Shailendra,

  In MSSQL, you should format in the query itself. For this, you can use the CAST and/or CONVERT functions. A short example below will show you the difference if you run them in SQLCMD.

  select name, database_id from sys.databases

  select cast(name as varchar(35)), database_id from sys.databases

  -- Erik http://blog.rollback.hu

• jshailendra

  SSCrazy

  Points: 2260

  More actions

  January 20, 2010 at 10:07 pm

  #1106476

  Thanks Erik for your help