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

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

Let me hear your thoughts!

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.