Я выполняю некоторые административные запросы и компилирую результаты из sp_spaceused в SQL Server 2008, чтобы посмотреть на отношение данных/индексных пространств некоторых таблиц в моей базе данных. Конечно, я получаю всевозможные большие числа в результатах, и мои глаза начинают блестеть. Было бы очень удобно, если бы я мог форматировать все эти числа запятыми (987654321 становится 987 654 321). Забавно, что за многие годы, которые я использовал SQL Server, эта проблема никогда не возникала, так как большую часть времени я делал форматирование на уровне презентации, но в этом случае результат T-SQL в SSMS равен.
Я решил просто создать простой CLR UDF, чтобы решить эту проблему, но похоже, что это должно выполняться только в простом старом T-SQL. Итак, я поставил вопрос здесь - как вы делаете числовое форматирование в ванильном T-SQL?
Пока я согласен со всеми, включая OP, который говорит, что форматирование должно выполняться на уровне презентации, это форматирование может быть выполнено в T-SQL путем нажатия на money, а затем преобразования в varchar. Это включает в себя завершающие десятичные знаки, хотя это можно отбросить с помощью SUBSTRING.
SELECT CONVERT(varchar, CAST(987654321 AS money), 1)
Я бы рекомендовал заменить вместо подстроки, чтобы избежать проблем с длиной строки:
REPLACE(CONVERT(varchar(20), (CAST(SUM(table.value) AS money)), 1), '.00', '')
В SQL Server 2012 и выше это будет форматировать число с запятыми:
select format([Number], 'N0')
Вы также можете изменить 0 на количество десятичных знаков, которое вы хотите.
Для реализаций SQL Server 2012+ у вас будет возможность использовать FORMAT для применения форматирования строк к нестрочным типам данных.
В исходном вопросе пользователь запросил возможность использования запятых в качестве разделителей тысяч. В закрытом как повторяющийся вопрос пользователь спросил, как они могут применять форматирование валюты. Следующий запрос показывает, как выполнять обе задачи. Он также демонстрирует применение культуры, чтобы сделать это более общим решением (обращаясь к функции Циридиса Димитриса, чтобы применить греческое специальное форматирование)
-- FORMAT
-- http://msdn.microsoft.com/en-us/library/hh213505(v=sql.110).aspx
-- FORMAT does not do conversion, that 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 для вышеуказанного
Демонстрирует добавление запятых:
PRINT FORMATMESSAGE('The number is: %s', format(5000000, '#,##0'))
-- Output
The number is: 5,000,000
Демонстрирует запятые и десятичные точки. Обратите внимание, что при необходимости округляет последнюю цифру.
PRINT FORMATMESSAGE('The number is: %s', format(5000000.759145678, '#,##0.00'))
-- Output
The number is: 5,000,000.76
SQL Server 2012+.
SELECT REPLACE(CONVERT(varchar(20), (CAST(9876543 AS money)), 1), '.00', '')
output = 9,876,543
и вы можете заменить 9876543 на имя вашего столбца.
Пробовал денежный трюк выше, и это отлично подходит для числовых значений с двумя или менее значимыми цифрами. Я создал свою собственную функцию для форматирования чисел с десятичными знаками:
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
вот еще один t-sql UDF
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')`
Вот скалярная функция, которую я использую, которая исправляет некоторые ошибки в предыдущем примере (см. выше), а также обрабатывает десятичные значения (до указанного числа) (EDITED также работает с 0 и отрицательными номерами). Еще одно замечание: метод броска в виде денег ограничен размером данных типа MONEY и не работает с четырьмя (или более) цифрами. Этот метод определенно проще, но менее гибким.
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
Другой UDF, который, как мы надеемся, достаточно общий и не делает предположений о том, хотите ли вы округлить до определенного количества знаков после запятой:
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
-- =================================================================== --
/* Эта функция требует 3 аргумента: первый аргумент - это @Numero_str, который вводит Number как данные, а остальные 2 аргумента определяют, как будет форматироваться информация для вывода, эти аргументы - @Pos_Enteros и @Pos_Decimales, которые определяют, сколько целых чисел и Десятичные числа, которые вы хотите показать для номера, который вы передаете в качестве входного аргумента. */
Это относится к комментарию к Phil Hunt, но, увы, у меня нет репутации.
Чтобы вырезать ".00" с конца вашей числовой строки, parseame супер-удобно. Он токенизирует строки с разделителями периодов и возвращает указанный элемент, начиная с самого правого маркера в качестве элемента 1.
SELECT PARSENAME(CONVERT(varchar, CAST(987654321 AS money), 1), 2)
Доходность "987 654 321"