Quick and dirty: How to right-align numeric columns in SSMS

2018-06-122018-06-12 / Daniel Hutmacher

Here are 50 random numbers:

--- 50 random numbers
WITH cte AS (
SELECT 0 AS i, 100000.*POWER(RAND(CHECKSUM(NEWID())), 3) AS n
UNION ALL
SELECT i+1, 100000.*POWER(RAND(CHECKSUM(NEWID())), 3)
FROM cte WHERE i<50)

SELECT *
FROM cte;

And in SSMS, with the variable-width default font, the output looks… slightly-less-than-readable in the grid view:

We could use STR() to format the output, but the indent looks a little off:

--- 50 random numbers
WITH cte AS (
SELECT 0 AS i, 100000.*POWER(RAND(CHECKSUM(NEWID())), 3) AS n
UNION ALL
SELECT i+1, 100000.*POWER(RAND(CHECKSUM(NEWID())), 3)
FROM cte WHERE i<50)

SELECT *, STR(n, 12, 2) AS with_str
FROM cte;

Here’s something I’ve found: the space character is roughly about half the width of a typical number character. So replace every leading space with two spaces, and it will look really neat in the grid:

--- 50 random numbers
WITH cte AS (
SELECT 0 AS i, 100000.*POWER(RAND(CHECKSUM(NEWID())), 3) AS n
UNION ALL
SELECT i+1, 100000.*POWER(RAND(CHECKSUM(NEWID())), 3)
FROM cte WHERE i<50)

SELECT *, STR(n, 12, 2) AS with_str,
       REPLACE(STR(n, 12, 2), ' ', '  ') AS with_replace
FROM cte;

(and terrible everywhere else, obviously.)

14 thoughts on “Quick and dirty: How to right-align numeric columns in SSMS”

Pingback: Right-Aligning Numbers In Management Studio – Curated SQL
jpvh

2018-06-18 at 16:25

You could just set the Results to Text (Ctrl+T) and then enable the “Right align numeric values” option.

Reply
- Daniel Hutmacher
  
  2018-06-18 at 17:05
  
  Yeah, but the text results are not as pretty. ;)
  
  Reply
Chuck Hamilton

2018-06-18 at 18:10

Or just put the results into Excel and right justify the column. :)

Here’s an equally Q&D trick.Create “graphs” in ssms using a string constant and the left() function. In the simple example below, somecol represents a column that could contain a value from 1-20.

select somecol, left(‘####################’,somecol)
from sometable;

Reply
Stork

2018-06-19 at 00:20

I use a non-proportional font in both text and grid views. That way, a space is exactly the same size as any single digit. Personally, I can’t stand proportional font in my results.

Reply
Rob MacMorran

2018-06-19 at 02:42

OR you could switch to a fixed width font…

Tools -> Options -> Environment -> Fonts and Colors -> Show settings for: Grid Results -> Font: (any bolded fixed width font)

Reply
- Dan F
  
  2019-01-03 at 03:20
  
  you… wait… you can change that? Rob MacMorran I could hug you!
  
  Reply
Brad Schulz

2018-12-17 at 20:17

Cute… but don’t forget negative numbers. That negative sign is also about half the width of the numbers, so you’ll need to add another REPLACE() to replace the hyphen with a space-hyphen.

Reply
Pingback: Right-align numeric values in SSMS grid | CJay's SQL Server, Endeca, MS Office, and Entrinsik's Informer Help
Tony

2020-09-20 at 17:40

Wow this is cool!
For me REPLACE(STR(n, 12, 2), ‘ ‘, ‘ ‘) AS with_replace worked right away!
But I would like to format the number from: 1234567890.12 to 1,234,567,890.12 keeping the aligment
Thanks!

Reply
Amy

2020-12-09 at 12:47

This worked with me , thanks,
But I have the same Problem as Tony . I Need to Format it to ‘de-de’ 1234567890.12 to 1.234.567.890,12 while keeping the alignment.

Is there any way to make both ?

Thanks

Reply
- Daniel Hutmacher
  
  2020-12-09 at 13:15
  
  That post was mostly meant as a joke. You should consider doing the formatting and alignment in the output layer/report.
  That said, you could try
  CONVERT(varchar(20), CONVERT(money, amount), 1)
  The conversion from the money datatype to varchar, with the ”1” modifier at the end, creates the thousand-separator you’re looking for. After that, just add the leading spaces. :)
  
  Reply
  - Amy Tanious
    
    2020-12-10 at 11:46
    
    When I convert it to Money, the right Align goes away.
    and when I convert to money first and then try tor align to the right , I get a problem with the STR function as it is expecting Float.
    Go you have any suggestion for this please ?
    
    REPLACE(STR(((ISNULL(Sum(Umsatz),0))),20,2),’ ‘, ‘ ‘) ‘Umsatz’, => this works correct to the right without the formating of the thousands and decimal is a point and not comma^
    Replace(Convert(varchar(20),(Convert(Money,(ISNULL(Sum(Umsatz),0)))),1),’ ‘, ‘ ‘) ‘UmsatzVarchar’ => this doesn’t make it to the right
    (Convert(Money,REPLACE(STR(((ISNULL(Sum(Umsatz),0))),20,2),’ ‘, ‘ ‘))) ‘UmsatzMoney’, => this as well is not to the right.
    
    I have decimal numbers that are not equal in length.
    9,999,999.99
    3,700.00
    450.00
    1,780.00
    
    Thanks if you can help
    
    Reply
nexus64

2022-10-10 at 11:55

Nice trick… but now combine that with formatting :P

i.e.: REPLACE(STR(format((qrs.avg_duration / 1000 * qrs.count_executions), ‘N0’, ‘nl_nl’)), ‘ ‘, ‘ ‘) as Impact

Reply