6

Goal

Format a COUNT result to a common format (e.g. ###,##0) in a concise statement and without UDF's.

I've Tried

I am currently using something like this, though it leaves two decimals and is clunky:

SELECT CONVERT(VARCHAR, CAST(COUNT([id]) AS MONEY), 1) FROM tbl

The reason I went that direction is because it was the only standard formatting option I could find when reading through the CAST and CONVERT documentation from MSDN.

I don't really like it, but it limps along since these numbers are simply copied and pasted into other text editors and such after the T-SQL runs. They do of course have to be formatted by hand to remove the decimals in some places.

Research and Restrictions

Of course you could build a user-defined function, like this one on this blog, but I have a restriction that keeps me from building UDF's for this purpose.

After some additional research I found that if I were using SQL 2012 I could use a new T-SQL FORMAT function, alas, I'm restricted to 2008 R2. This of course leverages a different platform as it's a .NET interface. :D

I am also aware of this solution: Format a number with commas but without decimals in SQL Server 2008 R2?

Community
  • 1
  • 1
Mike Perrenoud
  • 66,820
  • 29
  • 157
  • 232
  • I'm sure you're aware of this, and I don't want to question anyone with so much rep, but this really is handled *so* much easier in your application, unless you're absolutely hell-bent on doing it in your SQL query – LittleBobbyTables - Au Revoir Aug 15 '13 at 18:35
  • 2
    Why don't you perform this formatting on the client, e.g. C# has very good functionality in Format() and ToString(). Also [please stop declaring `varchar` without length. It's lazy and dangerous](https://sqlblog.org/blogs/aaron_bertrand/archive/2009/10/09/bad-habits-to-kick-declaring-varchar-without-length.aspx). – Aaron Bertrand Aug 15 '13 at 18:35
  • @LittleBobbyTables, I'm restricted to `T-SQL` in this case. I would, as you stated, do it in .NET if I had the luxury. However, this is a massive conversion script that must be executed on the SQL server because of restrictions out of my control. – Mike Perrenoud Aug 15 '13 at 18:36
  • @AaronBertrand, I'm restricted to using `T-SQL` in this case. I would love to, of course, leverage the .NET framework here to make my life a little easier. If I could do the entire conversion using .NET I would, but alas, I've been restricted by the powers that be. – Mike Perrenoud Aug 15 '13 at 18:39
  • 1
    So what is wrong with the existing solution? You want something more concise? More efficient? Something else? If it ain't broke, I don't think you're going to find anything "better" other than formatting on the client. – Aaron Bertrand Aug 15 '13 at 18:48
  • @AaronBertrand, yes, I'm looking for something more concise. I've done a lot of research already, and I've tried to update that section of my question with it, and I too feel I'm up against a wall. However, I thought that some of my peers up here might know something I don't. – Mike Perrenoud Aug 15 '13 at 18:50
  • I don't think you can make anything much shorter, no. – Aaron Bertrand Aug 15 '13 at 18:52

2 Answers2

11

Even though the question author was stuck on SQL Server 2008R2, this is a concise and efficient solution should you be blessed with SQL Server 2012 or newer.

No digits after decimal point (rounded)

SELECT FORMAT(COUNT([id]), N'N0')
FROM [tbl]

Example output

53,234,568

One digit after decimal point

SELECT FORMAT(COUNT([id]), N'N1')
FROM [tbl]

Example output

53,234,567.9

Two digits after decimal point

SELECT FORMAT(COUNT([id]), N'N2')
FROM [tbl]

Example output

53,234,567.89

(etc.)

Two digits after decimal point, no thousands separator

SELECT FORMAT(COUNT([id]), N'#.00')
FROM [tbl]

Example output

53234567.89
Lars Gyrup Brink Nielsen
  • 3,939
  • 2
  • 34
  • 35
9

Not the most elegant, but you can remove the trailing .00 with replace.

SELECT REPLACE(CONVERT(VARCHAR, CAST(COUNT([id]) AS MONEY), 1), '.00', '')
FROM tbl
Community
  • 1
  • 1
Chris Rodriguez
  • 888
  • 7
  • 16