<p>I'm looking to use SQL to format a number with commas in the thousands, but no decimal (so can't use Money) - any suggestions?</p>

<p>I'm using SQL Server 2005, but feel free to answer for others as well (like MySQL)</p>

[{'answer_id': 42209, 'author': 'ceejayoz', 'author_id': 1902010, 'author_profile': 'https://Stackoverflow.com/users/1902010', 'pm_score': 2, 'selected': False, 'text': '<p>In MySQL, the <a href="http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_format" rel="nofollow noreferrer"><code>FORMAT()</code></a> function will do the trick.</p>\n'}, {'answer_id': 42224, 'author': 'Scott Nichols', 'author_id': 4299, 'author_profile': 'https://Stackoverflow.com/users/4299', 'pm_score': 2, 'selected': False, 'text': '<p>With TSQL you could cast to money and convert it will add the .00, but you could use replace or substring to remove.</p>\n\n<pre><code>replace(convert(varchar, cast(column as money), 1), \'.00\', \'\')\n</code></pre>\n\n<p>In SQL 2005 you could use a CLR function as well</p>\n\n<pre><code>[Microsoft.SqlServer.Server.SqlFunction]\npublic static SqlString FormatNumber(SqlInt32 number)\n{\n    return number.Value.ToString("N0");\n}\n</code></pre>\n\n<p>and call it as any other user-defined function</p>\n\n<pre><code>SELECT dbo.FormatNumber(value)\n</code></pre>\n'}, {'answer_id': 42233, 'author': 'hamishmcn', 'author_id': 3590, 'author_profile': 'https://Stackoverflow.com/users/3590', 'pm_score': 2, 'selected': False, 'text': "<p>In Oracle you can specify a format parameter to the to_char function:<br>\nTO_CHAR(1234, '9,999')  -->  1,234</p>\n"}, {'answer_id': 42238, 'author': 'nickd', 'author_id': 2373, 'author_profile': 'https://Stackoverflow.com/users/2373', 'pm_score': 0, 'selected': False, 'text': "<p>For SQL Server, you could format the number as money and then delete the right-most three characters.</p>\n\n<pre><code>replace(convert (varchar, convert (money, 109999), 1), '.00','')\n</code></pre>\n"}, {'answer_id': 42258, 'author': 'Kevin Fairchild', 'author_id': 3743, 'author_profile': 'https://Stackoverflow.com/users/3743', 'pm_score': 2, 'selected': False, 'text': "<p>Any specific reason you want this done on the server side?  Seems like it is a task better suited for the client/report.</p>\n\n<p>Otherwise, you are <strong>storing a number as a string</strong> just so you can keep the formatting how you want it -- but you just lost the ability to do even basic arithmetic on it without having to reconvert it to a number.</p>\n\n<p>If you're really determined to do it in SQL and have a justifiable reason for it, I guess my vote is on Scott's method: Value --> Money --> Varchar --> Trim off the decimal portion</p>\n\n<p>-- Kevin Fairchild</p>\n"}]
['https://Stackoverflow.com/questions/42203', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/357/']