Format to Currency with Comma in SQL Server Query

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

Lawrence SalvucciInformation Technology ManagerAsked:
Who is Participating?
I wear a lot of hats...


Pawan KumarDatabase ExpertCommented:
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 ExpertCommented:
One more.. if you are using SQL 2012 and above. Easy one.

SELECT FORMAT(1234567.80, '$##,##0.00')
Jim HornSQL Server Data DudeCommented:
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.
Introduction to Web Design


Lawrence SalvucciInformation Technology ManagerAuthor 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.
Pawan KumarDatabase ExpertCommented:
Yes the first option will work with SQL Server 2008. that is..

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

Experts Exchange Solution brought to you by

Your issues matter to us.


Start your 7-day free trial
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
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.
Microsoft SQL Server

From novice to tech pro — start learning today.