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