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

14 comments

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

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

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

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

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

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

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

    1. 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. ๐Ÿ™‚

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

  8. Nice trick… but now combine that with formatting ๐Ÿ˜›

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

Leave a comment

Your email address will not be published. Required fields are marked *