Select convert to 2 decimal places, comma, right aligned

Question

Post reply

Select convert to 2 decimal places, comma, right aligned

Tania Mofflin

Ten Centuries

Points: 1021
More actions
May 29, 2014 at 8:25 pm

#307778

Hello
The field below currently has an output that looks like this -
select
A.[User_Info_Currency_1] as [Replacement Cost]
from EQSA0345 A
Replacement Cost
523.88000
60689.76000
48860.26000
77239.26000
132227.26000
65668.18000
71523.34000
I would like the output to be right aligned, 2 decimal places and have a comma as below
Replacement Cost 523.88 60,689.76 48,860.26 77,239.26 132,227.26 65,668.18 71,523.34
Is there a list of all the decimal formats? I have found one which lists all the different date formats but can't find any for decimals.
I have found many other forum posts on this topic but they all give an output not quite in the format I need, I would like to learn myself the different formats rather than just copying someone else's code.
http://www.sql-server-helper.com/tips/date-formats.aspx">
http://www.sql-server-helper.com/tips/date-formats.aspx
I also understand it's best to format the raw data outside of sql which I usually do but this is an exception.
Thankyou

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 1

Formatting is usually best done in the front end tool.

Is this for a report?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

dbalmf SSCrazy Points: 2063 More actions · Answer 2

I agree this should be done in the frontend app.

This code will give you the commas and decimal places

SELECT CONVERT(VARCHAR, CAST('1234567890' AS MONEY),1)

As for left or right justification, that will depend on how you are presenting the data to the end user

============================================================
David

http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

chad 62627 Old Hand Points: 330 More actions · Answer 3

Okay look...I know this is 5 years later...but I googled this issue, and this forum post is the #2 item in the google search results.
I just figured out one way to do this, and I thought I would throw it in here for anyone else who also performs the same google search as me.

This won't work for everyone. I have my management studio set to use a fixed width font for my grid results. If you're not using a fixed width font, it will look funky.

You can use STR() to right justify numbers, but if you want to add commas, then that causes issues. And this just occurred to me and it works great:

SELECT REVERSE(CONVERT(CHAR(10), REVERSE(FORMAT(COUNT(*),'N'))))

NOTE: Due to the use of two REVERSE functions and the FORMAT function, this is not recommended for use on queries that will produce a large number of rows. I'm personally using this for a query that outputs a small number of rows (<5000 rows) so for me, the performance hit is not a problem.

I'm adding commas using the FORMAT() function, reversing it, then converting to a CHAR(10) to pad spaces, then reversing again. This will produce a right justified number with commas, but only if you are using a fixed width font on your grid results.

Hope one day this helps someone 🙂

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 4

chad 62627 - Thursday, January 3, 2019 3:34 PM
Okay look...I know this is 5 years later...but I googled this issue, and this forum post is the #2 item in the google search results.
I just figured out one way to do this, and I thought I would throw it in here for anyone else who also performs the same google search as me.
This won't work for everyone. I have my management studio set to use a fixed width font for my grid results. If you're not using a fixed width font, it will look funky.
You can use STR() to right justify numbers, but if you want to add commas, then that causes issues. And this just occurred to me and it works great:
SELECT REVERSE(CONVERT(CHAR(10), REVERSE(FORMAT(COUNT(*),'N'))))
I'm adding commas using the FORMAT() function, reversing it, then converting to a CHAR(10) to pad spaces, then reversing again. This will produce a right justified number with commas, but only if you are using a fixed width font on your grid results.
Hope one day this helps someone 🙂

Just so you know, FORMAT is an average of 44 times slower than cast or convert, Using 2 reversals isn't going to help performance there, either.

You also don't want to use STR() for similar reasons.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 100948 More actions · Answer 5

select right(space(12)+convert(varchar(20),cast([User_Info_Currency_1] as money),1), 12) as [Replacement Cost] from ( values( 523.88000),( 60689.76000),( 48860.26000),( 77239.26000),( 132227.26000),( 65668.18000),( 71523.34000) ) as EQSA0345(User_Info_Currency_1)

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

chad 62627 Old Hand Points: 330 More actions · Answer 6

Jeff Moden - Thursday, January 3, 2019 10:07 PM
Just so you know, FORMAT is an average of 44 times slower than cast or convert, Using 2 reversals isn't going to help performance there, either.
You also don't want to use STR() for similar reasons.

Yes, I'm aware it's much slower. But for my personal uses, speed wasn't an issue as I was only dealing with a couple thousand rows. I just wanted to post up my findings in case it might help someone else who was looking for a similar solution.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 7

chad 62627 - Tuesday, January 8, 2019 2:02 PM
Jeff Moden - Thursday, January 3, 2019 10:07 PM
Just so you know, FORMAT is an average of 44 times slower than cast or convert, Using 2 reversals isn't going to help performance there, either.
You also don't want to use STR() for similar reasons.
Yes, I'm aware it's much slower. But for my personal uses, speed wasn't an issue as I was only dealing with a couple thousand rows. I just wanted to post up my findings in case it might help someone else who was looking for a similar solution.

You KNEW of the performance problem and posted it anyway? .

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

chad 62627 Old Hand Points: 330 More actions · Answer 8

Jeff Moden - Tuesday, January 8, 2019 6:45 PM
You KNEW of the performance problem and posted it anyway? .

Yes, like I said, for my personal uses, it wasn't a problem. It's only a problem if you're using it on a large number of rows. I'm using this for a query I occasionally run to grab some quick statistics and it outputs a small number of rows..

I'll edit my post and note the performance issue if it will make you happy as I probably should have noted it anyways 🙂

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 9

chad 62627 - Wednesday, January 9, 2019 10:01 AM
Jeff Moden - Tuesday, January 8, 2019 6:45 PM
You KNEW of the performance problem and posted it anyway? .
Yes, like I said, for my personal uses, it wasn't a problem. It's only a problem if you're using it on a large number of rows. I'm using this for a query I occasionally run to grab some quick statistics and it outputs a small number of rows..
I'll edit my post and note the performance issue if it will make you happy as I probably should have noted it anyways 🙂

I guess my point is... why would you write something that has a known performance issue just because you're using a smaller number of rows? If you write enough of such code, then the system dies of a thousand cuts like the ones I've been fixing in the legacy code for the companies that I work for.

If it's worth doing, it's worth doing right... all the time.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)