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中进行数字格式化?
另一个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
试试上面的金钱技巧，这对于两个或更少有效数字的数值非常有效。我创建了自己的函数来用小数格式化数字:
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
