pad out results with 0's - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

pad out results with 0's

Author

Topic

jamesbeal
Starting Member

5 Posts

Posted - 2011-05-01 : 07:29:09

Hi,

I've only ever done simple SQL queries really and I need a bit of help. I'm doing the below query but I want to pad the fields Pages + Records out with 0's so its always 5 digits, can anyone help???

SELECT [CompanyCode], [SalesOffice], SUM([Pages]) [Pages], SUM([Records]) [Records] FROM [Emtex].[dbo].[ASDValidation] GROUP BY [CompanyCode], [SalesOffice]

Thanks All

J Beal

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-01 : 07:51:30

Many people on this forum would advise you that doing that type of formatting is better done at the client side, such as in the presentation layer. However, if you do need it, you need it - so you can do it as shown below:

SELECT [CompanyCode], [SalesOffice],
right('00000'+cast(SUM([Pages]) as varchar(32)),5) [Pages],
right('00000'+cast(SUM([Records]) as varchar(32)),5) [Records] 
FROM [Emtex].[dbo].[ASDValidation] GROUP BY [CompanyCode], [SalesOffice]

jamesbeal
Starting Member

5 Posts

Posted - 2011-05-01 : 08:56:14

Cheers that sorted it, Many Thanks.

James

J Beal

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources