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

User · Answer

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!

User · Answer

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

User · Answer

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

User · Answer

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 gt 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

User · Answer

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

[sql] SQL Server format decimal places with commas

Examples related to sql

Examples related to sql-server