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.)
Pingback: Right-Aligning Numbers In Management Studio – Curated SQL
You could just set the Results to Text (Ctrl+T) and then enable the “Right align numeric values” option.
Yeah, but the text results are not as pretty. ;)
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;
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.
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)
you… wait… you can change that? Rob MacMorran I could hug you!
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.
Pingback: Right-align numeric values in SSMS grid | CJay's SQL Server, Endeca, MS Office, and Entrinsik's Informer Help
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!