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

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

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.