[sql] SQL Server format decimal places with commas

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

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

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

FORMAT(@s,'#,0.0000')

[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.

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.

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!