SQL Server format decimal places with commas

Question

How can I convert the decimal values to have some commas?

this Helps me. But my problem the decimal places are set to 2 only..I want the decimal to be 2, 3, or 4..example

1,234.123   or    1,234.12345

I tried

convert(varchar, convert(decimal(18, 4), 1234.1234567), 1)

Output : 1234.1234

There is no comma. But if I use money the decimal are 2 only

convert(varchar, convert(money, 1234.1234567), 1)

Output : 1,234.12

Numbers don't have formats. They gain formatting ***only*** when you convert them to a string, which is done for presentation. And your data layer should ***not*** be concerned about presentation. That's ummm, what a presentation layer is for. From SQL you should return the native data type and only convert it for presentation in your front end. *[This aids debugging, performance, architecture through decoupling, maintainability, flexibility/adaptability, etc, etc...]* — MatBailie, Nov 06 '12 at 08:41
Agreed about the presentation layer, but there are valid cases. I have to construct ad hoc queries in SSMS and then email the results. This is one less step I need to take care of before emailing. — Darren Griffith, Jan 05 '15 at 21:05
I agree; the formatting should be done in the presentation layer. But SSMS IS the presentation layer here. I aslo write ad-hoc queries, and then paste the result into a spreadsheet for the "end-user", ie boss and/or accountant. I now struggle because SSMS makes decimal points, and the spreadsheet requires decimal commas. Ideally, I should either by a "SET DECIMAL_FORMAT="DA_dk" or some option in SSMS be able to change the decimal format.. — Leif Neland, May 27 '16 at 11:29

RichardTheKiwi · Answer 1 · 2012-11-06T08:36:58.997

37

Thankfully(?), in SQL Server 2012+, you can now use FORMAT() to achieve this:

FORMAT(@s,'#,0.0000')

In prior versions, at the risk of looking real ugly

[Query]:

declare @s decimal(18,10);
set @s = 1234.1234567;
select replace(convert(varchar,cast(floor(@s) as money),1),'.00',
    '.'+right(cast(@s * 10000 +10000.5 as int),4))

In the first part, we use MONEY->VARCHAR to produce the commas, but FLOOR() is used to ensure the decimals go to .00. This is easily identifiable and replaced with the 4 digits after the decimal place using a mixture of shifting (*10000) and CAST as INT (truncation) to derive the digits.

[Results]:

|   COLUMN_0 |
--------------
| 1,234.1235 |

But unless you have to deliver business reports using SQL Server Management Studio or SQLCMD, this is NEVER the correct solution, even if it can be done. Any front-end or reporting environment has proper functions to handle display formatting.

edited Nov 06 '12 at 08:36

answered Nov 06 '12 at 08:21

RichardTheKiwi

105,798
26
196
262

1

At first pass, I couldn't find a link to the documentation that describes the specific formatting convention (for the '#,0.0000' string). The link *is* buried in the page you reference, but it took me a while to find it. For the convenience of others, here it is: [Custom Numeric Format Strings](https://msdn.microsoft.com/library/0c899ak8.aspx) – kmote Jan 13 '16 at 14:06
You could alternatively use this as the `.00` replacement string : `SUBSTRING( CONVERT(VarChar,@s-FLOOR(@s)+0.00005) ,2,1+4)` which will give you the `.1235` - I find that more obvious with regard to the actual rounding amount applied, although perhaps not quite so directly clear about the `+4` number of decimal places specified. And of course this only works correctly with positive amounts :) – AjV Jsy Jan 05 '17 at 13:13

score 15 · Accepted Answer · answered Nov 06 '12 at 07:28

without considering this to be a good idea...

select dbo.F_AddThousandSeparators(convert(varchar, convert(decimal(18, 4), 1234.1234567), 1))

Function

-- Author:      bummi
-- Create date: 20121106
CREATE FUNCTION F_AddThousandSeparators(@NumStr varchar(50)) 
RETURNS Varchar(50)
AS
BEGIN
declare @OutStr varchar(50)
declare @i int
declare @run int

Select @i=CHARINDEX('.',@NumStr)
if @i=0 
    begin
    set @i=LEN(@NumStr)
    Set @Outstr=''
    end
else
    begin   
     Set @Outstr=SUBSTRING(@NUmStr,@i,50)
     Set @i=@i -1
    end 


Set @run=0

While @i>0
    begin
      if @Run=3
        begin
          Set @Outstr=','+@Outstr
          Set @run=0
        end
      Set @Outstr=SUBSTRING(@NumStr,@i,1) +@Outstr  
      Set @i=@i-1
      Set @run=@run + 1     
    end

    RETURN @OutStr

END
GO

Upvoted for actually giving a solution whilst warning that is not the best way to do this! — callisto, Nov 06 '12 at 07:42

score 10 · Answer 3 · edited May 23 '17 at 10:31

10

From a related SO question: Format a number with commas but without decimals in SQL Server 2008 R2?

SELECT CONVERT(varchar, CAST(1112 AS money), 1)

This was tested in SQL Server 2008 R2.

edited May 23 '17 at 10:31

Community

1
1

answered Jan 05 '15 at 21:11

Darren Griffith

3,290
3
28
35

Money is considered a bad format and it tends to fudge up results in certain cases when performing certain operations with these values. Even Microsoft themselves recommend NOT using that type. – Jurijs Kastanovs Nov 10 '17 at 10:17
1

@JurijsKastanovs, I agree, don't do any calculations or store data using money. But, if you have a specific requirement to format a number in the data layer (maybe for display in SSMS), this is one way to do it. It should be the last operation performed on the data. – Darren Griffith Dec 15 '17 at 21:46

score 2 · Answer 4 · edited Mar 31 '23 at 17:55

If you are using SQL Azure Reporting Services, the "format" function is unsupported. This is really the only way to format a tooltip in a chart in SSRS. So the workaround is to return a column that has a string representation of the formatted number to use for the tooltip. So, I do agree that SQL is not the place for formatting. Except in cases like this where the tool does not have proper functions to handle display formatting.

In my case I needed to show a number formatted with commas and no decimals (type decimal 2) and ended up with this gem of a calculated column in my dataset query:

,Fmt_DDS=reverse(stuff(reverse(CONVERT(varchar(25),cast(SUM(kv.DeepDiveSavingsEst) as money),1)), 1, 3, ''))

It works, but is very ugly and non-obvious to whoever maintains the report down the road. Yay Cloud!

score -1 · Answer 5 · answered Nov 06 '12 at 07:10

-1

SQL (or to be more precise, the RDBMS) is not meant to be the right choice for formatting the output. The database should deliver raw data which then should be formatted (or more general: processed) in the destination application.

However, depending on the specific system you use, you may write a UDF (user defined function) to achive what you want. But please bear in mind that you then are in fact returning a varchar, which you will not be able to further process (e.g. summarize).

answered Nov 06 '12 at 07:10

alzaimar

4,572
1
16
30

2

"SQL..is not meant to be the right choice for formatting the output" - Yes, that is the actual issue. Formatting output on the UI side is much easier too. – callisto Nov 06 '12 at 07:41
This is not an answer. This just says that it is possible to do. – Darren Griffith Jan 05 '15 at 21:06
Reiterating what D-Money said, this is not an answer. – Alban Apr 15 '16 at 14:08
Why should I give an example how *NOT* to do it? – alzaimar Apr 20 '16 at 11:57

SQL Server format decimal places with commas

5 Answers5

Linked

Related