How do I format a number with commas in T-SQL

Question

I m running some administrative queries and compiling results from sp spaceused in SQL Server 2008 to look at data index space ratios of some tables in my database   Of course I am getting all sorts of large numbers in the results and my eyes are starting to gloss over   It would be really convenient if I could format all those numbers with commas  987654321 becomes 987 654 321    Funny that in all the many years I ve used SQL Server  this issue has never come up since most of the time I would be doing formatting at the presentation layer  but in this case the T-SQL result in SSMS is the presentation   I ve considered just creating a simple CLR UDF to solve this  but it seems like this should be do-able in just plain old T-SQL   So  I ll pose the question here - how do you do numeric formatting in vanilla T-SQL

User · Answer

here is another t-sql UDF   CREATE FUNCTION dbo Format  num int  returns varChar 30  As Begin Declare  out varChar 30          while  num  gt  0 Begin       Set  out   str  num   1000  3  0    Coalesce      out            Set  num    num   1000   End   Return  out End

User · Answer

Demo 1  Demonstrates adding commas   PRINT FORMATMESSAGE  The number is   s   format 5000000       0    -- Output The number is  5 000 000   Demo 2  Demonstrates commas and decimal points  Observe that it rounds the last digit if necessary   PRINT FORMATMESSAGE  The number is   s   format 5000000 759145678       0 00    -- Output The number is  5 000 000 76   Compatibility  SQL Server 2012

User · Answer

Author  Tsiridis Dimitris       Greek amount format  For the other change the change on replace of      amp         CREATE FUNCTION dbo formatAmount     amtIn as varchar 20    RETURNS varchar 20  AS BEGIN   return cast REPLACE SUBSTRING CONVERT varchar 20   CAST  amtIn AS money   1  1  LEN CONVERT varchar 20   CAST  amtIn AS money   1  -3               replace RIGHT CONVERT varchar 20   CAST  amtIn AS money   1  3            AS VARCHAR 20    END  SELECT  geniki   dbo   formatAmount   9888777666555 44

User · Answer

------------------------------------------------------------------------                 SQL Query Script                                                             ----------------                                                  Funcion    dbo fn nDerecha   Numero  Pos Enteros  Pos Decimales                 Numero          es el Numero o Valor a formatear                             Pos Enteros     es la cantidad posiciones para Enteros                       Pos Decimales   es la cantidad posiciones para Decimales                                                                                               OBJETIVO   Formatear los Numeros con Coma y Justificado a la Derecha          Por Ejemplo                                                                   dbo fn nDerecha   Numero  9  2           Resultado   --- --- --9 99                      dado  Numero   1234 56       Resultado         1 234 56                      dado  Numero   -1 56         Resultado            -1 56                      dado  Numero   -53783423 56  Resultado   -53 783 423 56                                                                                     Autor      Francisco Eugenio Cabrera Perez                                   Fecha      Noviembre 25  2015                                                Pais       Republica Dominicana                                             ------------------------------------------------------------------------        CREATE FUNCTION  dbo   fn nDerecha        -- Agregue Argumentos  para personalizar la funcion a su conveniencia      Numero str    varchar max       Pos Enteros   int      Pos Decimales int   RETURNS varchar max  AS BEGIN   --  Declare la variable del RETURN aqui  en este caso es RESULT   declare  RESULTADO varchar max    set      RESULTADO             -----------------------------------------------  --   declare  Numero num numeric 28 12    set      Numero num         case when isnumeric  Numero str    0         then 0        else round  convert  numeric 28 12    Numero str    Pos Decimales    end       --  -----------------------------------------------  --   --  Aumenta  Pos Enteros de  RESULTADO    --      si las posiciones de Enteros del dato  Numero str es Mayor      --   declare    Num Pos Ent int   set        Num Pos Ent   len   convert  varchar  convert int  abs  Numero num          --   declare    Pos Ent Mas int   set        Pos Ent Mas         case when  Num Pos Ent  gt   Pos Enteros        then  Num Pos Ent -  Pos Enteros        else 0   end       set        Pos Enteros    Pos Enteros    Pos Ent Mas   --   --  -----------------------------------------------  --   declare  p Signo ctd       int   set      p Signo ctd          case when  Numero num  lt  1 then 1 else 0 end    --   declare  p Comas ctd       int   set      p Comas ctd            Pos Enteros - 1     3   --   declare  p Punto ctd       int   set      p Punto ctd          case when  Pos Decimales  gt  0 then 1 else 0 end    --   declare  p input Longitud  int   set      p input Longitud       p Signo ctd    Pos Enteros                                       p Punto ctd    Pos Decimales   --   declare  p output Longitud int   set      p output Longitud      p Signo ctd    Pos Enteros      p Comas ctd                                     p Punto ctd    Pos Decimales     --   --                                                                       --     declare  Valor str varchar max    set      Valor str   str  Numero num   p input Longitud   Pos Decimales     declare  V Ent str varchar max    set      V Ent str       case when  Pos Decimales  gt  0          then substring   Valor str  0  charindex       Valor str  0            else             Valor str end    --   declare  V Dec str varchar max    set      V Dec str       case when  Pos Decimales  gt  0          then       right  Valor str   Pos Decimales          else    end    --   set  V Ent str   convert VARCHAR  convert money   V Ent str   1     set  V Ent str   substring   V Ent str  0  charindex       V Ent str  0      --     set  RESULTADO       V Ent str    V Dec str    --   set  RESULTADO     replicate        p output Longitud - len  RESULTADO       RESULTADO     --    --                                                                       -         --                                                                       -    RETURN  RESULTADO END    --                                                                       --      This function needs 3 arguments  the First argument is the  Numero str which the Number as data input  and the other 2 arguments specify how the information will be formatted for the output  those arguments are  Pos Enteros and  Pos Decimales which specify how many Integers and Decimal places you want to show for the Number you pass as input argument

User · Answer

Another UDF which is hopefully generic enough and does not make assumptions about whether you want to round to a specific number of decimal places   CREATE FUNCTION  dbo   fn FormatNumber    number decimal 38 18    RETURNS varchar 50   BEGIN     -- remove minus sign before applying thousands seperator     DECLARE  negative bit     SET  negative   CASE WHEN  number  lt  0 THEN 1 ELSE 0 END     SET  number   ABS  number       -- add thousands seperator for every 3 digits to the left of the decimal place     DECLARE  pos int   result varchar 50    CAST  number AS varchar 50       SELECT  pos   CHARINDEX       result      WHILE  pos  gt  4     BEGIN         SET  result   STUFF  result   pos-3  0               SELECT  pos   CHARINDEX       result      END      -- remove trailing zeros     WHILE RIGHT  result  1     0          SET  result   LEFT  result  LEN  result -1      -- remove decimal place if not required     IF RIGHT  result  1                SET  result   LEFT  result  LEN  result -1       IF  negative   1         SET  result    -     result      RETURN  result END

User · Answer

While I agree with everyone  including the OP  who says that formatting should be done in the presentation layer  this formatting can be accomplished in T-SQL by casting to money and then converting to varchar  This does include trailing decimals  though  that could be looped off with SUBSTRING   SELECT CONVERT varchar  CAST 987654321 AS money   1

User · Answer

Tried the money trick above  and this works great for numerical values with two or less significant digits  I created my own function to format numbers with decimals   CREATE FUNCTION  dbo   fn FormatWithCommas         -- Add the parameters for the function here      value varchar 50    RETURNS varchar 50  AS BEGIN     -- Declare the return variable here     DECLARE  WholeNumber varchar 50    NULL   Decimal varchar 10         CharIndex int   charindex       value       IF   CharIndex  gt  0          SELECT  WholeNumber   SUBSTRING  value  1   CharIndex-1    Decimal   SUBSTRING  value   CharIndex  LEN  value       ELSE         SET  WholeNumber    value      IF LEN  WholeNumber   gt  3          SET  WholeNumber   dbo fn FormatWithCommas SUBSTRING  WholeNumber  1  LEN  WholeNumber -3           RIGHT  WholeNumber  3         -- Return the result of the function     RETURN  WholeNumber    Decimal  END

User · Answer

This belongs in a comment to Phil Hunt s answer but alas I don t have the rep   To strip the   00  off the end of your number string  parsename is super-handy    It tokenizes period-delimited strings and returns the specified element  starting with the rightmost token as element 1   SELECT PARSENAME CONVERT varchar  CAST 987654321 AS money   1   2    Yields  987 654 321

User · Answer

Please try with below query   SELECT FORMAT 987654321          0     Format with right decimal point    SELECT FORMAT 987654321          0

User · Answer

For SQL Server 2012  implementations  you will have the ability to use the FORMAT to apply string formatting to non-string data types    In the original question  the user had requested the ability to use commas as thousands separators  In a closed as duplicate question  the user had asked how they could apply currency formatting  The following query shows how to perform both tasks  It also demonstrates the application of culture to make this a more generic solution  addressing Tsiridis Dimitris s function to apply Greek special formatting   -- FORMAT -- http   msdn microsoft com en-us library hh213505 v sql 110  aspx -- FORMAT does not do conversion  that s the domain of cast convert parse etc -- Only accepts numeric and date time data types for formatting   -- -- Formatting Types -- http   msdn microsoft com en-us library 26etazsy aspx  -- Standard numeric format strings -- http   msdn microsoft com en-us library dwhawy9k aspx SELECT     -- c   gt  currency     -- n   gt  numeric     FORMAT 987654321  N N   C culture  AS some number     FORMAT 987654321  N c   C culture  AS some currency     C culture FROM               -- Language culture names         -- http   msdn microsoft com en-us library ee825488 v cs 20  aspx         VALUES               en-US                 en-GB                 ja-JP                 Ro-RO                 el-GR         C  culture     SQLFiddle for the above

User · Answer

For SQL Server before 2012 which does not include the FORMAT function  create this function   CREATE FUNCTION FormatCurrency  value numeric 30 2       RETURNS varchar 50      AS     BEGIN         DECLARE  NumAsChar VARCHAR 50          SET  NumAsChar         CONVERT varchar 50   CAST  Value AS money  1          RETURN  NumAsChar     END    select dbo FormatCurrency 12345678  returns  12 345 678 00  Drop the   if you just want commas

User · Answer

I d recommend Replace in lieu of Substring to avoid string length issues   REPLACE CONVERT varchar 20    CAST SUM table value  AS money    1     00

User · Answer

In SQL Server 2012 and higher  this will format a number with commas   select format  Number    N0     You can also change 0 to the number of decimal places you want

User · Answer

SELECT REPLACE CONVERT varchar 20    CAST 9876543 AS money    1     00         output   9 876 543  and you can replace 9876543 by your column name

User · Answer

Here is a scalar function I am using that fixes some bugs in a previous example  above  and also handles decimal values  to the specified   of digits   EDITED to also work with 0  amp  negative numbers    One other note  the cast as money method above is limited to the size of the MONEY data type  and doesn t work with 4  or more  digits decimals   That method is definitely simpler but less flexible   CREATE FUNCTION  dbo   fnNumericWithCommas   num decimal 38  18    decimals int   4  RETURNS varchar 44  AS BEGIN     DECLARE  ret varchar 44       DECLARE  negative bit  SET  negative   CASE WHEN  num  lt  0 THEN 1 ELSE 0 END      SET  num   abs round  num   decimals   -- round the value to the number of decimals desired     DECLARE  decValue varchar 18   SET  decValue   substring ltrim  num - round  num  0  1      000000000000000000   3   decimals      SET  num   round  num  0  1  -- truncate the incoming number of any decimals     WHILE  num  gt  0 BEGIN         SET  ret   str  num   1000  3  0    isnull      ret              SET  num   round  num   1000  0  1      END     SET  ret   isnull replace ltrim  ret         0     0            decValue     IF   negative   1  SET  ret    -     ret      RETURN  ret END  GO

[sql-server] How do I format a number with commas in T-SQL?

Examples related to sql-server

Examples related to tsql