Post reply

Column width formating in text output

  • 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

  • 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

  • January 20, 2010 at 10:07 pm

    #1106476

    Thanks Erik for your help

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply