sql-server - numero - formato 2 decimales sql server

¿Cómo formateo un número con comas en T-SQL?

Question

Estoy ejecutando algunas consultas administrativas y recopilando resultados de sp_spaceused en SQL Server 2008 para ver las proporciones de espacio de índice / datos de algunas tablas en mi base de datos. Por supuesto, estoy obteniendo todo tipo de números grandes en los resultados y mis ojos están empezando a desvanecerse. Sería realmente conveniente si pudiera formatear todos esos números con comas (987654321 se convierte en 987,654,321). Es curioso que en todos los años que he usado SQL Server, este problema nunca haya surgido ya que la mayoría de las veces estaría formateando en la capa de presentación, pero en este caso el resultado de T-SQL en SSMS es la presentación.

Consideré simplemente crear un UDF de CLR simple para resolver esto, pero parece que esto debería ser posible en el viejo y simple T-SQL. Entonces, plantearé la pregunta aquí: ¿cómo se formatea numéricamente en T-SQL vainilla?

Answer 1

Demo 1

Demuestra agregar comas:

PRINT FORMATMESSAGE(''The number is: %s'', format(5000000, ''#,##0''))
-- Output
The number is: 5,000,000

Demo 2

Demuestra comas y puntos decimales. Observe que redondea el último dígito si es necesario.

PRINT FORMATMESSAGE(''The number is: %s'', format(5000000.759145678, ''#,##0.00''))
-- Output
The number is: 5,000,000.76

Compatibilidad

SQL Server 2012+ .

Answer 2

Aquí hay una función escalar que estoy usando que corrige algunos errores en un ejemplo anterior (arriba) y también maneja valores decimales (al número de dígitos especificado) (EDITADO también funciona con 0 y números negativos). Otra nota, el método de reparto como dinero anterior está limitado al tamaño del tipo de datos MONEY, y no funciona con 4 (o más) dígitos decimales. Ese método es definitivamente más simple pero menos 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 < 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 > 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

Answer 3

En SQL Server 2012 y superior, esto formateará un número con comas:

select format([Number], ''N0'')

También puede cambiar 0 a la cantidad de decimales que desee.

Answer 4

Esto pertenece a un comentario de la respuesta de , pero lamentablemente no tengo el representante.

Para quitar el ".00" del final de la cadena de números, parsename es súper útil. Token cadenas de caracteres delimitadas por períodos y devuelve el elemento especificado, comenzando por el token de la derecha como elemento 1.

SELECT PARSENAME(CONVERT(varchar, CAST(987654321 AS money), 1), 2)

Rinde "987,654,321"

Answer 5

Intenté el truco de dinero anterior, y esto funciona muy bien para valores numéricos con dos o menos dígitos significativos. Creé mi propia función para formatear números con decimales:

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 > 0)
        SELECT @WholeNumber = SUBSTRING(@value, 1, @CharIndex-1), @Decimal = SUBSTRING(@value, @CharIndex, LEN(@value))
    ELSE
        SET @WholeNumber = @value

    IF(LEN(@WholeNumber) > 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

Answer 6

Intente con la siguiente consulta:

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

Formatear con el punto decimal derecho:

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

Answer 7

Otra UDF que, afortunadamente, es lo suficientemente genérica y no hace suposiciones sobre si desea redondear a un número específico de decimales:

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

Answer 8

Para las implementaciones de SQL Server 2012+, tendrá la capacidad de utilizar FORMAT para aplicar formato de cadenas a tipos de datos que no sean cadenas.

En la pregunta original, el usuario había solicitado la posibilidad de usar comas como separadores de miles. En una pregunta cerrada como duplicada , el usuario había preguntado cómo podían aplicar el formato de moneda. La siguiente consulta muestra cómo realizar ambas tareas. También demuestra la aplicación de la cultura para hacer de esta una solución más genérica (abordando la función de Tsiridis Dimitris para aplicar el formato especial griego)

-- 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 para el anterior

Answer 9

Recomiendo reemplazar en lugar de subcadena para evitar problemas de longitud de cadena:

REPLACE(CONVERT(varchar(20), (CAST(SUM(table.value) AS money)), 1), ''.00'', '''')

Answer 10

Si bien estoy de acuerdo con todo el mundo, incluido el OP, que dice que el formateo debe hacerse en la capa de presentación, este formato se puede realizar en T-SQL al convertirlo en money y luego convertirlo a varchar . Esto incluye los decimales finales, sin embargo, que se pueden SUBSTRING con SUBSTRING .

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

Answer 11

aquí hay otro UDF t-sql

CREATE FUNCTION dbo.Format(@num int)
returns varChar(30)
As
Begin
Declare @out varChar(30) = ''''

  while @num > 0 Begin
      Set @out = str(@num % 1000, 3, 0) + Coalesce('',''+@out, '''')
      Set @num = @num / 1000
  End
  Return @out
End

Answer 12

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

  --  ===================================================================  --

/ * Esta función necesita 3 argumentos: el primer argumento es @Numero_str que el Número como entrada de datos, y los otros 2 argumentos especifican cómo se formateará la información para el resultado, esos argumentos son @Pos_Enteros y @Pos_Decimales que especifican cuántos Números enteros y decimales que desea mostrar para el número que pasa como argumento de entrada. * /

Answer 13

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

salida = 9,876,543

y puede reemplazar 9876543 por su nombre de columna.

Answer 14

`/* Author: Tsiridis Dimitris */
/* Greek amount format. For the other change the change on replace of ''.'' & '','' */
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'')`