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中进行数字格式化?
对于SQL Server 2012+实现，您将能够使用FORMAT将字符串格式应用于非字符串数据类型。
在最初的问题中，用户要求能够使用逗号作为千位分隔符。在一个封闭的重复问题中，用户询问他们如何应用货币格式。下面的查询显示了如何执行这两个任务。它还演示了区域性的应用，以使其成为更通用的解决方案(解决Tsiridis Dimitris应用希腊特殊格式的功能)
-- 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用于上面
演示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+。
试试上面的金钱技巧，这对于两个或更少有效数字的数值非常有效。我创建了自己的函数来用小数格式化数字:
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
