Format to Currency with Comma in SQL Server Query

Lawrence Salvucci
Lawrence Salvucci used Ask the Experts™
on
I've been on a formatting binge today with my query! I need to format this to currency but it also needs to include the comma (eg $2,000.00 instead of $2000.00). Right now I have it formatting as $2000.00 but I want the comma included. How can I tweak this to format it with the comma?

CASE WHEN LEFT(dbo.jomast.fjobno, 1) <> 'I' THEN '$' + CONVERT(varchar(20), 
                         CONVERT(money, dbo.sorels.funetprice * dbo.sorels.forderqty)) ELSE '0' END AS ExtPrice

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Try below

SELECT CONCAT( '$' , REPLACE(CONVERT(varchar(20), (CAST(987654321 AS money)), 1), '.00', '') )

SELECT CONCAT( '$' , FORMAT(CAST(987654321 AS MONEY), N'N', 'en-US') )
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
One more.. if you are using SQL 2012 and above. Easy one.

SELECT FORMAT(1234567.80, '$##,##0.00')
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
What are you building this for?  Reason I ask is because it's a best practice to let the presentation layer handle cosmetic formatting, and the data layer to just store the data.   Also, altering the format will convert the number into a character data type, which means you lose the ability to perform math expressions and aggregates like SUM().

So .. if this is for a report, I HIGHLY ( *** HIGH - LEE *** ) recommend doing the cosmetic formatting in the report and not the database.

If this is for exporting to a file, then the above solutions should work for you.
Lawrence SalvucciDirector of Information Technology

Author

Commented:
I tried putting one of your first 2 solutions in my query and it added a { fn } to that portion of the query. I replaced your '987654321' with my fields and added the CASE statement from the beginning of my original code. Am I missing something? We are using SQL 2008 so we have to go with one of your first solutions.
Database Expert
Awarded 2016
Top Expert 2016
Commented:
Yes the first option will work with SQL Server 2008. that is..


SELECT '$' + REPLACE(CONVERT(varchar(20), (CAST(987654321 AS money)), 1), '.00', '')
Lawrence SalvucciDirector of Information Technology

Author

Commented:
Jim,
I appreciate your comments and I totally understand where you are coming from. This is actually being exported to a file. Otherwise I wouldn't waste doing all this formatting.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial