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

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.)

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

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

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

  3. 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;

  4. 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.

  5. 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)

  6. 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.

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

  8. 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!

  9. 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

    • 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. :)

      • 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

Leave a Reply to Tony Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.