blocks|key|3006364|text|在SQL+Server+2012和更高版本中，这将使用逗号格式化数字：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3006365|select+format([Number],+'N0')|code-block|syntax|javascript|3006366|您还可以将0更改为所需的小数位数。|offset|length|style|CODE|3006367|entityMap^0|0|0|5|1|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@$I|R|J|S|K|L]]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

In SQL Server 2012 and higher, this will format a number with commas:

<pre><code>select format([Number], 'N0')
</code></pre>

You can also change <code>0</code> to the number of decimal places you want.

blocks|key|3219959|text|虽然我同意包括OP在内的每个人的观点，他们认为格式化应该在表示层完成，但在T-SQL中，可以通过转换为money，然后转换为varchar来完成这种格式化。但是，这确实包括尾随小数，可以用SUBSTRING循环删除。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|3219960|SELECT+CONVERT(varchar,+CAST(987654321+AS+money),+1)|code-block|syntax|javascript|3219961|entityMap^0|1F|5|1Q|7|2M|9|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]|$9|P|A|Q|B|C]|$9|R|A|S|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|T|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|U|8|@]|D|@]|E|$]]]|L|$]]

While I agree with everyone, including the OP, who says that formatting should be done in the presentation layer, this formatting can be accomplished in T-SQL by casting to <code>money</code> and then converting to <code>varchar</code>. This does include trailing decimals, though, that could be looped off with <code>SUBSTRING</code>.

<pre><code>SELECT CONVERT(varchar, CAST(987654321 AS money), 1)
</code></pre>

blocks|key|286813|text|我建议替换掉Substring，以避免字符串长度问题：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|286814|REPLACE(CONVERT(varchar(20),+(CAST(SUM(table.value)+AS+money)),+1),+'.00',+'')|code-block|syntax|javascript|286815|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

I'd recommend Replace in lieu of Substring to avoid string length issues:

<pre><code>REPLACE(CONVERT(varchar(20), (CAST(SUM(table.value) AS money)), 1), '.00', '')
</code></pre>

blocks|key|3006284|text|对于SQL+Server+2012%2B实现，您将能够使用FORMAT将字符串格式应用于非字符串数据类型。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|3006285|在最初的问题中，用户要求能够使用逗号作为千位分隔符。在closed+as+duplicate+question中，用户询问如何应用货币格式。下面的查询显示了如何执行这两个任务。它还演示了文化的应用，以使其成为更通用的解决方案(解决Tsiridis+Dimitris的函数以应用希腊特殊格式)|3006286|--+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);|code-block|syntax|javascript|3006287|用于上述内容的SQLFiddle|3006288|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/en-us/library/hh213505%2528v=sql.110%2529.aspx|1|https://stackoverflow.com/questions/16039825/format-dollar-amount-for-a-tsql-query|2|http://sqlfiddle.com/#!6/d41d8/3545/0^0|R|6|0|0|R|S|1|0|0|7|9|2|0^^$0|@$1|2|3|4|5|6|7|Y|8|@]|9|@$A|Z|B|10|1|11]]|C|$]]|$1|D|3|E|5|6|7|12|8|@]|9|@$A|13|B|14|1|15]]|C|$]]|$1|F|3|G|5|H|7|16|8|@]|9|@]|C|$I|J]]|$1|K|3|L|5|6|7|17|8|@]|9|@$A|18|B|19|1|1A]]|C|$]]|$1|M|3|-4|5|6|7|1B|8|@]|9|@]|C|$]]]|N|$O|$5|P|Q|R|C|$S|T]]|U|$5|P|Q|R|C|$S|V]]|W|$5|P|Q|R|C|$S|X]]]]

For SQL Server 2012+ implementations, you will have the ability to use the <a href="http://msdn.microsoft.com/en-us/library/hh213505%28v=sql.110%29.aspx" rel="noreferrer">FORMAT</a> to apply string formatting to non-string data types. 

In the original question, the user had requested the ability to use commas as thousands separators. In a <a href="https://stackoverflow.com/questions/16039825/format-dollar-amount-for-a-tsql-query">closed as duplicate question</a>, the user had asked how they could apply currency formatting. The following query shows how to perform both tasks. It also demonstrates the application of culture to make this a more generic solution (addressing Tsiridis Dimitris's function to apply Greek special formatting)

<pre><code>-- 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 =&gt; currency
 -- n =&gt; 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);
</code></pre>

<a href="http://sqlfiddle.com/#!6/d41d8/3545/0" rel="noreferrer">SQLFiddle</a> for the above

blocks|key|3220193|text|演示1|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3220194|演示如何添加逗号：|3220195|PRINT+FORMATMESSAGE('The+number+is:+%25s',+format(5000000,+'#,##0'))
--+Output
The+number+is:+5,000,000|code-block|syntax|javascript|3220196|演示2|3220197|演示逗号和小数点。注意，如果需要，它会对最后一位进行舍入。|3220198|PRINT+FORMATMESSAGE('The+number+is:+%25s',+format(5000000.759145678,+'#,##0.00'))
--+Output
The+number+is:+5,000,000.76|3220199|兼容性|3220200|SQL+Server+2012%2B。|offset|length|style|CODE|3220201|entityMap^0|0|0|0|0|0|0|0|0|G|0^^$0|@$1|2|3|4|5|6|7|Y|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Z|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|10|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|11|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|12|8|@]|9|@]|A|$]]|$1|M|3|N|5|F|7|13|8|@]|9|@]|A|$G|H]]|$1|O|3|P|5|6|7|14|8|@]|9|@]|A|$]]|$1|Q|3|R|5|6|7|15|8|@$S|16|T|17|U|V]]|9|@]|A|$]]|$1|W|3|-4|5|6|7|18|8|@]|9|@]|A|$]]]|X|$]]

<h3>Demo 1</h3>

Demonstrates adding commas:

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

<h3>Demo 2</h3>

Demonstrates commas and decimal points. Observe that it rounds the last digit if necessary.

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

<h3>Compatibility</h3>

<code>SQL Server 2012+</code>.

blocks|key|289667|text|请尝试使用以下查询：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|289668|SELECT+FORMAT(987654321,'#,###,##0')|code-block|syntax|javascript|289669|使用右小数点设置格式：|289670|SELECT+FORMAT(987654321,'#,###,##0.###\,###')|289671|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

Please try with below query:

<pre><code>SELECT FORMAT(987654321,'#,###,##0')
</code></pre>

Format with right decimal point :

<pre><code>SELECT FORMAT(987654321,'#,###,##0.###\,###')
</code></pre>

blocks|key|3220215|text|SELECT+REPLACE(CONVERT(varchar(20),+(CAST(9876543+AS+money)),+1),+'.00',+'')|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|3220216|output=+9,876,543|unstyled|3220217|您可以将9876543替换为您的列名。|3220218|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|L|8|@]|9|@]|A|$]]|$1|G|3|H|5|F|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|F|7|N|8|@]|9|@]|A|$]]]|J|$]]

<pre><code>SELECT REPLACE(CONVERT(varchar(20), (CAST(9876543 AS money)), 1), '.00', '')
</code></pre>

output= 9,876,543

and you can replace 9876543 by your column name.

blocks|key|3219968|text|尝试了上面的钱的把戏，这对有两个或更少有效数字的数值很有效。我创建了自己的函数，将数字格式化为小数：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3219969|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))+%2B+','+%2B+RIGHT(@WholeNumber,+3)



++++--+Return+the+result+of+the+function
++++RETURN+@WholeNumber+%2B+@Decimal

END|code-block|syntax|javascript|3219970|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Tried the money trick above, and this works great for numerical values with two or less significant digits. I created my own function to format numbers with decimals:

<pre><code>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 &gt; 0)
 SELECT @WholeNumber = SUBSTRING(@value, 1, @CharIndex-1), @Decimal = SUBSTRING(@value, @CharIndex, LEN(@value))
 ELSE
 SET @WholeNumber = @value

 IF(LEN(@WholeNumber) &gt; 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
</code></pre>

blocks|key|288771|text|这篇评论属于Phil+Hunt的回答，但可惜我没有这方面的代表。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|288772|要去掉数字字符串末尾的".00“，parsename是非常方便的。它标记句点分隔的字符串并返回指定的元素，从最右边的标记开始作为元素1。|288773|SELECT+PARSENAME(CONVERT(varchar,+CAST(987654321+AS+money),+1),+2)|code-block|syntax|javascript|288774|收益率"987,654,321“|288775|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/users/492543/phil-hunt^0|6|9|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@$A|V|B|W|1|X]]|C|$]]|$1|D|3|E|5|6|7|Y|8|@]|9|@]|C|$]]|$1|F|3|G|5|H|7|Z|8|@]|9|@]|C|$I|J]]|$1|K|3|L|5|6|7|10|8|@]|9|@]|C|$]]|$1|M|3|-4|5|6|7|11|8|@]|9|@]|C|$]]]|N|$O|$5|P|Q|R|C|$S|T]]]]

This belongs in a comment to <a href="https://stackoverflow.com/users/492543/phil-hunt">Phil Hunt</a>'s answer but alas I don't have the rep.

To strip the ".00" off the end of your number string, parsename is super-handy. 
 It tokenizes period-delimited strings and returns the specified element, starting with the rightmost token as element 1.

<pre><code>SELECT PARSENAME(CONVERT(varchar, CAST(987654321 AS money), 1), 2)
</code></pre>

Yields "987,654,321"

blocks|key|3006258|text|下面是另一个t-sql+UDF。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3006259|CREATE+FUNCTION+dbo.Format(@num+int)
returns+varChar(30)
As
Begin
Declare+@out+varChar(30)+=+''

++while+@num+>+0+Begin
++++++Set+@out+=+str(@num+%25+1000,+3,+0)+%2B+Coalesce(','%2B@out,+'')
++++++Set+@num+=+@num+/+1000
++End
++Return+@out
End|code-block|syntax|javascript|3006260|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

here is another t-sql UDF 

<pre><code>CREATE FUNCTION dbo.Format(@num int)
returns varChar(30)
As
Begin
Declare @out varChar(30) = ''

 while @num &gt; 0 Begin
 Set @out = str(@num % 1000, 3, 0) + Coalesce(','+@out, '')
 Set @num = @num / 1000
 End
 Return @out
End
</code></pre>

blocks|key|3219996|text|`/*+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),+',','.')
+%2B+replace(RIGHT(CONVERT(varchar(20),+CAST(@amtIn+AS+money),+1),3),+'.',',')+AS+VARCHAR(20))

END

SELECT+[geniki].[dbo].[formatAmount]('9888777666555.44')`|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|3219997|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>`/* Author: Tsiridis Dimitris */
/* Greek amount format. For the other change the change on replace of '.' &amp; ',' */
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')`
</code></pre>

blocks|key|291946|text|这是我正在使用的一个标量函数，它修复了前面例子(上面)中的一些bug，还可以处理十进制值(到指定的位数)(编辑后也可以处理0和负数)。另请注意，上面的强制转换为money方法仅限于MONEY数据类型的大小，并且不适用于4位(或更多)数字小数。这种方法肯定更简单，但灵活性较差。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|291947|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))+%2B+'000000000000000000',+3,+@decimals)
++++SET+@num+=+round(@num,+0,+1)+--+truncate+the+incoming+number+of+any+decimals
++++WHILE+@num+>+0+BEGIN
++++++++SET+@ret+=+str(@num+%25+1000,+3,+0)+%2B+isnull(','%2B@ret,+'')
++++++++SET+@num+=+round(@num+/+1000,+0,+1)
++++END
++++SET+@ret+=+isnull(replace(ltrim(@ret),+'+',+'0'),+'0')+%2B+'.'+%2B+@decValue
++++IF+(@negative+=+1)+SET+@ret+=+'-'+%2B+@ret

++++RETURN+@ret
END

GO|code-block|syntax|javascript|291948|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Here is a scalar function I am using that fixes some bugs in a previous example (above) and also handles decimal values (to the specified # of digits) (EDITED to also work with 0 &amp; negative numbers). One other note, the cast as money method above is limited to the size of the MONEY data type, and doesn't work with 4 (or more) digits decimals. That method is definitely simpler but less flexible.

<pre><code>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 &lt; 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 &gt; 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
</code></pre>

blocks|key|3006349|text|另一个UDF，希望它足够通用，并且不会假设您是否要舍入到特定的小数位数：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3006350|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+=+'-'+%2B+@result

++++RETURN+@result
END|code-block|syntax|javascript|3006351|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Another UDF which is hopefully generic enough and does not make assumptions about whether you want to round to a specific number of decimal places:

<pre><code>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 &lt; 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 &gt; 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
</code></pre>

blocks|key|288742|text|/*
++#------------------------------------------------------------------------#
++#++++++++++++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+%2B+@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+%2B+@Pos_Enteros+)+%2B
+++++++++++++++++++++++++++++++++@p_Punto_ctd+%2B+@Pos_Decimales
++--
++declare+@p_output_Longitud+int
++set+++++@p_output_Longitud+=+(+@p_Signo_ctd+%2B+@Pos_Enteros+++%2B+@p_Comas_ctd+)
+++++++++++++++++++++++++++++%2B+(+@p_Punto_ctd+%2B+@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+'.'+%2B+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+%2B+@V_Dec_str+
++--
++set+@RESULTADO+=+(+replicate(+'+',+@p_output_Longitud+-+len(@RESULTADO)+)+%2B+@RESULTADO+)
++--

++--++===================================================================++-|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|288743|--+===================================================================+-|unstyled|288744|++RETURN+@RESULTADO
END

++--++===================================================================++--|288745|/*此函数需要3个参数:第一个参数是@Numero_str，将数字作为数据输入，其他两个参数指定信息将如何格式化以用于输出，这些参数是@Pos_Enteros和@Pos_Decimales，它们指定要为作为输入参数传递的数字显示多少整数和小数位。*/|288746|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|N|8|@]|9|@]|A|$]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$B|C]]|$1|I|3|J|5|F|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|F|7|Q|8|@]|9|@]|A|$]]]|L|$]]

<pre><code>/*
 #------------------------------------------------------------------------#
 # 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 &gt; @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 &lt; 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 &gt; 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 &gt; 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 &gt; 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 )
 --

 -- =================================================================== -
</code></pre>

<h2> -- =================================================================== -</h2>

<pre><code> RETURN @RESULTADO
END

 -- =================================================================== --
</code></pre>

/*
This function needs 3 arguments: the First argument is the @Numero_str which the Number as data input, and the other 2 arguments specify how the information will be formatted for the output, those arguments are @Pos_Enteros and @Pos_Decimales which specify how many Integers and Decimal places you want to show for the Number you pass as input argument.
*/

blocks|key|3006519|text|对于不包含格式化功能的SQL+Server+2012之前版本，请创建以下函数：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3006520|CREATE+FUNCTION+FormatCurrency(@value+numeric(30,2))
++++RETURNS+varchar(50)
++++AS
++++BEGIN
++++++++DECLARE+@NumAsChar+VARCHAR(50)
++++++++SET+@NumAsChar+=+'$'+%2B+CONVERT(varchar(50),+CAST(@Value+AS+money),1)
++++++++RETURN+@NumAsChar
++++END+|code-block|syntax|javascript|3006521|select+dbo.FormatCurrency(12345678)返回$12,345,678.00|3006522|如果您只想要逗号，请去掉$。|3006523|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

For SQL Server before 2012 which does not include the FORMAT function, create this function:

<pre><code>CREATE FUNCTION FormatCurrency(@value numeric(30,2))
 RETURNS varchar(50)
 AS
 BEGIN
 DECLARE @NumAsChar VARCHAR(50)
 SET @NumAsChar = '$' + CONVERT(varchar(50), CAST(@Value AS money),1)
 RETURN @NumAsChar
 END 
</code></pre>

select dbo.FormatCurrency(12345678)
returns $12,345,678.00

Drop the $ if you just want commas.

I'm running some administrative queries and compiling results from <code>sp_spaceused</code> 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.

I've considered just creating a simple CLR UDF to solve this, but it seems like this should be do-able in just plain old T-SQL. So, I'll pose the question here - how do you do numeric formatting in vanilla T-SQL?

How do I format a number with commas in T-SQL?

我正在运行一些管理查询，并在SQL Server2008中编译来自sp_spaceused的结果，以查看数据库中某些表的数据/索引空间比率。当然，我在结果中得到了各种各样的大数字，我的眼睛开始变得模糊起来。如果我能用逗号格式化所有这些数字(987654321变成987,654,321)，那就太方便了。有趣的是，在我使用SQL Server的这么多年里，这个问题从来没有出现过，因为我大部分时间都是在

问如何在T-SQL中使用逗号格式化数字？
EN

回答 15

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在T-SQL中使用逗号格式化数字？EN

回答 15

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在T-SQL中使用逗号格式化数字？
EN