How to align selection of numbers?

Question

How to align selection of numbers?

P.M. Visser

SSC-Addicted

Points: 430
More actions
April 29, 2004 at 2:01 am

#107976

Migth be a silly question but I can't figure it out. How do I align numbers to the right side? My select is for instance "select size, maxsize from sysfiles". Default behaviour is that the output starts from the left side, how to make that from the right side? I'm not so familiar with tsql, I tried a lot with cast, rtrim, string and so on but ... it must be to simple I suppose. Can somebody tell me?

Viewing 6 posts - 1 through 5 (of 5 total)

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

Paul Cresham Hall of Fame Points: 3623 More actions · Answer 1

SQL Server does not format results to the left or right - this is dependant on what you're displaying the results in. For example, when Query Analyzer returns the results it does align to the left. I know of no way of changing this.

The only way to change to display on the right side is to format the results in whatever you're displaying them in.

David Burrows SSC Guru Points: 65141 More actions · Answer 2

Paul is correct, this sort of formatting is normally done in the user app.

However if you want to right align with leading spaces you can use

SELECT STR(12,10,0)

which will produce a char(10) right aligned no decimal

SELECT STR(12.75,10,2)

will produce a char(10) right aligned two decimal places

Far away is close at hand in the images of elsewhere.
Anon.

P.M. Visser SSC-Addicted Points: 430 More actions · Answer 3

Thanks David, that's what I was looking for! I put it together now like this:

select

substring(name,1,10) as Databasename,

substring(filename,1,50) as Filename,

str(size * 8) + N' KB' as Size,

str(maxsize * 8) + N' KB' as MaxSize,

str(growth * 8) + N' KB' as "Grow by",

str((maxsize - size) * 8) + N' KB' as "Free KB's",

str(round(((maxsize - size) * 100) / maxsize,0)) + N' %' as "Free PCT"

from sysfiles

And everything is aligned like I wanted. Also nice in osql, so I can produce nice overviews now with that output.

kknudson SSCarpal Tunnel Points: 4986 More actions · Answer 4

It's ugly but it works

LEFT(Convert(Char(16), CAST(TBRows AS Money), 1), 13) AS ' Rows',

The LEFT ... strips off the decimal points.

Using Money), 1 puts in the commas.

You can adjust the Char(16) and the Left(..., 13) bigger or smaller as needed. The left s/b Char -3 to strip the decimals

KlK

gljjr SSChampion Points: 10080 More actions · Answer 5

If you are using QA you can have it default to right align numerics by going to the Tools/Options menu. Then clicking on the Results tab and checking the Right align Numerics check box. Otherwise see the posts above

Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer

This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.