Come si formatta un numero con virgole in T-SQL?

Sto eseguendo alcune query amministrative e la compilazione dei risultati da sp_spaceused in SQL Server 2008 per esaminare i rapporti spazio dati / indice di alcune tabelle nel mio database. Certo che sto ottenendo un gran numero di grandi numeri nei risultati e i miei occhi stanno iniziando a sorvolare. Sarebbe davvero comodo se potessi formattare tutti quei numeri con virgole (987654321 diventa 987,654,321). Divertente che in tutti i molti anni in cui ho utilizzato SQL Server, questo problema non è mai venuto fuori dal momento che la maggior parte delle volte avrei eseguito la formattazione a livello di presentazione, ma in questo caso il risultato T-SQL in SSMS è la presentazione.

Ho preso in considerazione solo la creazione di un semplice UDF CLR per risolvere questo problema, ma sembra che questo dovrebbe essere in grado di fare solo in un semplice vecchio T-SQL. Quindi, porrò la domanda qui – come si fa la formattazione numerica in T-SQL vaniglia?

Mentre sono d’accordo con tutti, incluso l’OP, che dice che la formattazione dovrebbe essere eseguita nel livello di presentazione, questa formattazione può essere eseguita in T-SQL mediante il cast in money e la conversione in varchar . Ciò include anche i decimali finali, che potrebbero essere interrotti con SUBSTRING .

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

In SQL Server 2012 e versioni successive, questo formatta un numero con virgole:

 select format([Number], 'N0') 

Puoi anche cambiare 0 al numero di posizioni decimali che desideri.

Raccomanderei Sostituire al posto di Sottostringa per evitare problemi di lunghezza delle corde:

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

Per le implementazioni di SQL Server 2012+, sarà ansible utilizzare FORMAT per applicare la formattazione della stringa ai tipi di dati non stringa.

Nella domanda originale, l’utente aveva richiesto la possibilità di utilizzare virgole come separatori di migliaia. In una domanda chiusa come duplicata , l’utente aveva chiesto in che modo potevano applicare la formattazione della valuta. La seguente query mostra come eseguire entrambe le attività. Dimostra anche l’applicazione della cultura per rendere questa una soluzione più generica (affrontando la funzione di Tsiridis Dimitris per applicare la formattazione speciale greca)

 -- 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 per quanto sopra

Demo 1

Dimostra l’aggiunta di virgole:

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

Demo 2

Dimostra virgole e punti decimali. Osservare che arrotonda l’ultima cifra se necessario.

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

Compatibilità

SQL Server 2012+ .

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

produzione = 9,876,543

ed è ansible sostituire 9876543 con il nome della colonna.

Ho provato il trucco del denaro sopra, e questo funziona alla grande con valori numerici con due o meno cifre significative. Ho creato la mia funzione per formattare numeri con decimali:

 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 

Per favore prova con la seguente query:

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

Formato con punto decimale corretto:

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

ecco un’altra 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(','[email protected], '') Set @num = @num / 1000 End Return @out End 
 `/* 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')` 

Ecco una funzione scalare che sto usando che corregge alcuni bug in un precedente esempio (sopra) e gestisce anche i valori decimali (al numero di cifre specificato) (EDITED funziona anche con numeri 0 e negativi). Un’altra nota, il cast come metodo monetario sopra è limitato alla dimensione del tipo di dati MONEY e non funziona con 4 (o più) cifre decimali. Questo metodo è sicuramente più semplice ma meno flessibile.

 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(','[email protected], '') 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 

Un’altra UDF che si spera abbastanza generica e non fa ipotesi sul fatto che si desideri arrotondare a un numero specifico di cifre decimali:

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

/ * Questa funzione ha bisogno di 3 argomenti: il primo argomento è il @Numero_str che il Numero come input di dati, e gli altri 2 argomenti specificano come l’informazione sarà formattata per l’output, quegli argomenti sono @Pos_Enteros e @Pos_Decimales che specificano quanti Numeri interi e posizioni decimali che vuoi mostrare per il numero passato come argomento di input. * /

Questo appartiene a un commento alla risposta di Phil Hunt ma purtroppo non ho il rappresentante.

Per togliere il “.00” dalla fine della stringa numerica, il parsename è super pratico. Rende le stringhe delimitate periodicamente e restituisce l’elemento specificato, iniziando dal token più a destra come elemento 1.

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

Rendimenti “987.654.321”