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.
我考虑过只创建一个简单的CLR UDF来解决这个问题，但似乎这应该可以在普通的旧T-SQL中完成。所以，我将在这里提出一个问题-如何在香草T-SQL中进行数字格式化?
试试上面的金钱技巧，这对于两个或更少有效数字的数值非常有效。我创建了自己的函数来用小数格式化数字:
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
演示1
演示如何添加逗号:
PRINT FORMATMESSAGE('The number is: %s', format(5000000, '#,##0'))
-- Output
The number is: 5,000,000
演示2
演示逗号和小数点。注意，如果需要，它会四舍五入最后一位数字。
PRINT FORMATMESSAGE('The number is: %s', format(5000000.759145678, '#,##0.00'))
-- Output
The number is: 5,000,000.76
兼容性
SQL Server 2012+。
这里是我使用的标量函数，它修复了前面示例(上面)中的一些错误，还处理十进制值(指定的数字#)(编辑后也可用于0和负数)。另外需要注意的是，上面的转换为money方法仅限于money数据类型的大小，并且不适用于4(或更多)位小数。这种方法确实更简单，但不太灵活。
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
`/* 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')`
