January 20, 2010 at 2:55 am
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
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
Thanks Erik for your help
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy