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?

Answer 1

SELECT REPLACE(CONVERT(varchar(20), (CAST(9876543 AS money)), 1), '.00', '')

output= 9,876,543

and you can replace 9876543 by your column name.

Answer 2

/*
  #------------------------------------------------------------------------#
  #            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 > @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 < 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 > 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 > 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 > 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. */

Answer 3

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)

Answer 4

Please try with below query:

SELECT FORMAT(987654321,'#,###,##0')

Format with right decimal point :

SELECT FORMAT(987654321,'#,###,##0.###\,###')

Answer 5

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.

Answer 6

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 => currency
    -- n => 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