Format number with Thousands separator? 10000 --> 10,000

Question

 

I would like to select a BIGINT type and get a formatted result with commas.  Anyone have ideas?

 

declare @i bigint

set @i = 123456789

 

select @i

 

--Would like to get

123,456,789

Answer 1

Hi

 

There is no build-in fuctionality available.

 

If you do not need so often write a user defined function.

 

If you need better performance, write a CLR function.

 

Regards

Flo

Answer 2

Hi there,

 

This URL will answer your question. The author has an excellent book about User Defined Function.

http://novicksoftware.com/UDFofWeek/Vol1/T-SQL-UDF-Volume-1-Number-48-formatnumber.htm

 

Hope this helps,

Gonzalo

Answer 3

This is a bit 'unwieldy', but it seems to work.

(You could put it in a User Defined Function.)

 

DECLARE @MyValue bigint
SET @MyValue = 123456789156789

 

SELECT replace( convert( varchar(32), cast( '$' + cast( @MyValue AS varchar(32) ) AS money ), 1 ), '.00', '' )

 

--------------------------------------
123,456,789,156,789

 

Answer 4

 

And  the winner is:

 

 

DECLARE @MyValue bigint

SET @MyValue = 123456789

 

select replace(convert(varchar,convert(Money, @MyValue),1),'.00','')

Answer 5

This type of formatting should only be done in the GUI.  No one would ask for this in a file transfer or any type of feed.  SQL Server isn't the place to do this type of formatting even though it can be done.

---

--Jeff Moden

Answer 6

Jeff Moden said:

This type of formatting should only be done in the GUI.  No one would ask for this in a file transfer or any type of feed.  SQL Server isn't the place to do this type of formatting even though it can be done.

---

--Jeff Moden

100% agreed. This is a formatting issue and should be handled in the presentation layer where the appropriate formatting functions will no doubt exist and will be hundreds of times more efficient.

---

George

Answer 7

gvee said:

This is a formatting issue and should be handled in the presentation layer where the appropriate formatting functions will no doubt exist and will be hundreds of times more efficient.

---

I would not take such a hard-liner position on this issue.

While in principle I agree that the formatting should be done by the presentation layer such as Reporting Services reports, there are plenty of exceptions when, for one reason or another,  that solution is not available.

Assume you are the Senior DBA (database designer, developer) at company cDelta and the CFO request a revenue ad-hoc report to be emailed to him/her ASAP.  Your presentation layer is the email body. In such a case I would use the

SELECT ...., Revenue='$'+convert(varchar, RevenueCol, 1), ....

money to varchar conversion to make the report look presentable.

---

Kalman Toth, SQL Server 2005 Training, SSAS, SSIS, SSRS, BI: www.SQLUSA.com

Answer 8

Perhaps use SSRS report, with Currency format (or use Excel and currency/money format)

I agree that SQL should not be used for presentation, if possible.

---

Jerry Hung

Answer 9

SQLUSA said:

gvee said:

This is a formatting issue and should be handled in the presentation layer where the appropriate formatting functions will no doubt exist and will be hundreds of times more efficient.

---

I would not take such a hard-liner position on this issue.

While in principle I agree that the formatting should be done by the presentation layer such as Reporting Services reports, there are plenty of exceptions when, for one reason or another,  that solution is not available.

Assume you are the Senior DBA (database designer, developer) at company cDelta and the CFO request a revenue ad-hoc report to be emailed to him/her ASAP.  Your presentation layer is the email body. In such a case I would use the

SELECT ...., Revenue='$'+convert(varchar, RevenueCol, 1), ....

money to varchar conversion to make the report look presentable.

---

Kalman Toth, SQL Server 2005 Training, SSAS, SSIS, SSRS, BI: www.SQLUSA.com

Heh... you wouldn't be the Senior DBA for long because most CFO's are gonna want it in a spreadsheet.  That would be the GUI where you do the formatting.  And if you send it as a formatted text file with those bloody commas in it, you just know there's gonna be heck to pay when (s)he tries to import it into a spreadsheet.  CFO's don't want text files.

---

--Jeff Moden

Answer 10

Jeff Moden said:

you wouldn't be the Senior DBA for long because most CFO's are gonna want it in a spreadsheet.  That would be the GUI where you do the formatting.  n

Good pick Jeff, however, you missed the point. Yes, first time in his life the CFO did not want a spreadsheet attachment to the email....

Indeed Excel is the favorite presentation layer for ad-hoc reports prepared by senior DBAs/Developers.

I am still defending the

'$'+CONVERT(varchar, MoneyCol, 1)

currency conversion for one thing it is in the T-SQL language, so we cannot just ignore it.

I myself use it regularly when publishing results to the web.  I just don't like to publish currency data not formatted properly.  Example:

SELECT TOP(7)

ProductName=Name,

unformattedCost = StandardCost,

formattedCost = '$'+CONVERT(varchar,StandardCost,1),

Color

FROM AdventureWorks2008.Production.Product

WHERE Color is not null

ORDER BY ListPrice desc, ProductName

Results:

ProductName                   unformattedCost   formattedCost     Color

Road-150 Red, 44              2171.2942         $2,171.29         Red

Road-150 Red, 48              2171.2942         $2,171.29         Red

Road-150 Red, 52              2171.2942         $2,171.29         Red

Road-150 Red, 56              2171.2942         $2,171.29         Red

Road-150 Red, 62              2171.2942         $2,171.29         Red

Mountain-100 Silver, 38       1912.1544         $1,912.15         Silver

Mountain-100 Silver, 42       1912.1544         $1,912.15         Silver

I am also agreeing with everybody above who stated that formatting belongs to the presentation layer such as SSRS reports, crystal reports or even Excel worksheets.

---

Kalman Toth, SQL Server 2005 Training, SSAS, SSIS, SSRS, BI: www.SQLUSA.com

Answer 11

I've only ever recieved the request to have the data in an email once, which was promptly followed up by a phone call asking for it in a format they could copy into aspreadsheet ;)

But no, I completely appreciate the point you're making Kalman, but if you've got enough time to post the question to the interwebz and wait for a response, you have enough time to run a straight forward query in QA and copy the results into Excel, and format it in there! ;)

There are exceptions to every rule, but this is not one of them.

---

George

Answer 12

gvee said:

, you have enough time to run a straight forward query in QA and copy the results into Excel, and format it in there! ;)

I am afraid George Excel does not work for me when publishing to the web and trying to stay close to Management Studio results display.  See the example below. I just copy and paste a simple datatime value and see what happens:

select GETDATE()
/*

Result window in Management Studio:
2008-12-30 18:51:37.653

Excel 2007
51:37.7

*/

Here is SQL Server 2008 code to format a positive BIGINT number with thousand-separators:

DECLARE @BIGINT bigint = 9223372036854775807

DECLARE @strBIGINT varchar(32)=CONVERT(varchar(32), @BIGINT)

DECLARE @i tinyint = LEN(@strBIGINT) % 3 + 1

IF @i = 1 SET @i = 4

WHILE ( @i <= LEN(@strBIGINT))

BEGIN

SET @strBIGINT = STUFF(@strBIGINT, @i, 0, ',')

SET @i += 4

END

SELECT [FormattedBIGINT]= @strBIGINT

---

Kalman Toth, SQL Server 2005 Training, SSAS, SSIS, SSRS, BI: www.SQLUSA.com

Answer 13

Then you right click the column header > format > date/time :D
It was more advice to the OP to make sure they had considered this.

I don't feel either of us has to enter a debate to justify our suggestions, because they are both right! ;)

---

George

Answer 14

Hi

I've just encountered a similar problem myself, and thought it might be useful to share my solution.  Before everyone jumps on me, I know best practice is to format in the presentation layer, but in my (rather odd) case, this was not feasible - without boring you with details, I needed to use a DataGrid on the fly in c#, where the DataTables had relationships, so that the user could drill down through the hierarchy, with the effect that the column count varied and formatting became a huge pain!

My solution was a CLR function.  Because it took me some time to find out how to do this, I give the code below:

using

 

System.Collections;

using

 

System.Data.SqlTypes;

public

 

class SQLUtils

{

 

public static SqlString FN(SqlDouble num, SqlInt32 decPlaces)

{

 

double d = (double)num;

 

string format = "#,##0";

 

if (decPlaces > 0)

{

format +=

".";

 

for (int i = 0; i < decPlaces; i++)

{

format +=

"0";

}

}

 

SqlString formattedNumber = (SqlString)d.ToString(format);

 

return formattedNumber;

}

 

public static SqlString FN2(SqlDouble num, SqlInt32 decPlaces, SqlInt32 extraDecPlaces)

{

 

double d = (double)num;

 

string format = "#,##0";

 

bool noDec = true;

 

if (decPlaces > 0)

{

format +=

".";

noDec =

false;

 

for (int i = 0; i < decPlaces; i++)

{

format +=

"0";

}

}

 

if (extraDecPlaces > 0)

{

 

if (noDec)

{

format +=

".";

}

 

for (int i = 0; i < extraDecPlaces; i++)

{

format +=

"#";

}

}

 

SqlString formattedNumber = (SqlString)d.ToString(format);

 

return formattedNumber;

}

}

Then add the assembly (Right click Programmability/Assemblies and follow the wizard).

If not set, you must call sp_configure 'clr enabled', 1 followed by reconfigure.

Finally:

CREATE

 

FUNCTION [dbo].[FN](@num [float], @decPlaces [int])

RETURNS

 

[nvarchar](4000) WITH EXECUTE AS CALLER

AS

 

EXTERNAL

 

NAME [SQLHelper].[SQLUtils].[FN]

and

CREATE

 

FUNCTION [dbo].[FN2](@num [float], @decPlaces [int], @extraDecPlaces [int])

RETURNS

 

[nvarchar](4000) WITH EXECUTE AS CALLER

AS

 

EXTERNAL

 

NAME [SQLHelper].[SQLUtils].[FN2]

Now any float that needs to be formatted can be called SELECT dbo.FN(myfloat, 2) FROM myTable

NB the above creates scalar-valued functions, which need to be called with at least a two part name.

 

 

Answer 15

Remember the first rule of any tiered architecture?  Display formatting is always done in the front end and never in the database. What you are tryingto do is write 1950's COBOL in SQL; it was very good for this kind of thing. 

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

Answer 16

Thanks for your code.It's working.

Regards,

Karthick S.

Answer 17

This is a bit 'unwieldy', but it seems to work.

(You could put it in a User Defined Function.)

 

DECLARE @MyValue bigint
SET @MyValue = 123456789156789

 

SELECT replace( convert( varchar(32), cast( '$' + cast( @MyValue AS varchar(32) ) AS money ), 1 ), '.00', '' )

 

--------------------------------------
123,456,789,156,789

  

Thanks its working

 

Regards,

Karthick S.

Answer 18

The following articles are on similar topic:

http://www.sqlusa.com/bestpractices2005/moneyformat/

http://www.sqlusa.com/bestpractices2005/padleadingzeros/

UPDATE: SQL Server 2012 has introduced the FORMAT() function, borrowed from .NET languages. It does date, currency, number and percent formatting.

---

Kalman Toth SQL SERVER 2012 & BI TRAINING
New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

Answer 19

And if one HAS to emit email via DBMail one is left with, oh, let's think - T-SQL!  

"Display formatting s MOSTLY done in the front end and SOMETIMES in the database."  Thank goodness we have the ability in T-SQL!

The first RULE is sometimes BREAKABLE.  I need to show decimal(18,4) numbers in an email emitted from a very specialized SP and the CONVERT(...CAST(x AS money),1) won't cut it because it chops off 2 of my significant decimals.  A UDF or a CLR function WILL break the rule of necessity.

(I coded COBOL for a decade and more, and emitted 3270 screens with it - very good at it was jolly old COBOL on an IBM mainframe!  One just had to think outside the box...).

Answer 20

ABSOLUTELY AGREE, KALMAN.

When I see absolutes in threads like this, my first thought is "blinkers", ably demonstrated above.

Very sad.

I also need to emit formatted decimals in SMS messages.  No scope for a spreadsheet there (see post +2 below).

Sometimes our defense of a "position" leads to entrenchment beyond reason/reasonableness.

I wouldn't last in my DBA/Developer role if I didn't emit the emails/SMS messages to our clients, conveying CRUCIAL operating data, in a well-formatted manner!

Answer 21

For those of us tied to the DB for our formatting (SMS/email), the UDF above is excellent and very flexible and will go into Production today!

Thanks to Steve Strong and Andrew Novick for their vintage 2003 UDF.

Answer 22

For those of us tied to the DB for our formatting (SMS/email), the UDF above is excellent and very flexible and will go into Production today!

Thanks to Steve Strong and Andrew Novick for their vintage 2003 UDF.

Don't forget to decimal align it within a max column width setting for maximum beautification. ;-)

---

--Jeff Moden

Answer 23

Did that, too - wrote a custom version of a UDF I found that pads left/right/center/ends to a required length with a single nchar() that is either a regular space for text emails or an &nbsp; for HTML.

After boiling down the HTML to the simplest form that Notes, Gmail, Hotmail and Outlook will render decently, it may be repetitive-HTML but it looks "beautiful", especially compared to the pug-ugly text version we still have to emit for very-old-Lotus-Notes consumption.

Painful in T-SQL but FOR XML came in handy...

Production launch 10 minutes ago :-)

Answer 24

Just in case anyone thought my last post was meant to be sarcastic, it wasn't. ;-)  I was in a hurry.

Looking back on this years-old thread, I've changed a fair bit on this subject over the last few years.  While I agree that formatting should be done in an application if there is one, I don't always agree that you need an application just to do some formatting.  And it's really not that hard to do some rather nice HTML formatting so you can quickly get emails out without having to have externally managed code and the delays associated with running the likes of SSRS for such simple things.  The really cool part is that Excel likes to open such attachments as formatted spreadsheets.

Yep... compared to some of my older posts on this thread, I sound like a hypocrite and I apologize for that.  Let's just say that a bit of experience with needing to get "pretty" emails out in a hurry without turning it into a mini-reporting project has been a strong driving force and have changed my previous hard stance on the subject.  As with all else in this wonderful world of IT, "It Depends". ;-)

---

--Jeff Moden

Answer 25

Thanks, Jeff.  I didn't take it for sarcasm - my father was a master at sarcasm, not the insulting kind, the pointed humor kind (sorely miss him!)

Fully agree with your observations and conclusion.  Having near-beginner C# skills but a ton of T-SQL and being the only developer on staff, one has to use the tools available.  I like the way Microsoft has left us with so many opportunities for ingenuity - and that IS a tongue in cheek comment :=)

"It Depends" indeed...

Best regards,

Stephen

Answer 26

SQLUSA said:

gvee said:

This is a formatting issue and should be handled in the presentation layer where the appropriate formatting functions will no doubt exist and will be hundreds of times more efficient.

---

I would not take such a hard-liner position on this issue.

While in principle I agree that the formatting should be done by the presentation layer such as Reporting Services reports, there are plenty of exceptions when, for one reason or another,  that solution is not available.

Assume you are the Senior DBA (database designer, developer) at company cDelta and the CFO request a revenue ad-hoc report to be emailed to him/her ASAP.  Your presentation layer is the email body. In such a case I would use the

SELECT ...., Revenue='$'+convert(varchar, RevenueCol, 1), ....

money to varchar conversion to make the report look presentable.

---

Kalman Toth, SQL Server 2005 Training, SSAS, SSIS, SSRS, BI: www.SQLUSA.com

Heh... you wouldn't be the Senior DBA for long because most CFO's are gonna want it in a spreadsheet.  That would be the GUI where you do the formatting.  And if you send it as a formatted text file with those bloody commas in it, you just know there's gonna be heck to pay when (s)he tries to import it into a spreadsheet.  CFO's don't want text files.

---

--Jeff Moden

I am in that very situation. Our CFO request a report to be emailed to him periodically, because it's easier to watch it at his phone rather than an spreadsheet. But I'm not sending plain text, I'm sending an HTML table. I agree that formatting is better to be done in the presentation layer, but it's is not always possible.

Answer 27

But I'm not sending plain text, I'm sending an HTML table. I agree that formatting is better to be done in the presentation layer, but it's is not always possible.

The MS SQL Server development team is on your side. SQL Server 2012 has the new FORMAT() function borrowed from .NET languages. Now you can do currency formatting the proper way:

SELECT TopPrice = FORMAT(max(ListPrice),'c0', 'en-US')
FROM AdventureWorks2012.Production.Product;
-- $3,578

---

Kalman Toth SQL SERVER 2012 & BI TRAINING
New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

Answer 28

There is no one right or wrong answer to where to do formatting, you have to take it on a case by case basis.

My Date Dimension has both numbers and equivalent formatted strings stored directly in the relational table. This is a best practice.

I arrived here on this thread because of this one: Dimension Name Column Format Property SSAS 2005

Apparently someone needs to tell Microsoft where to do formatting because this was their response on that thread:

The "Format" string for Attribute names is a stub for a later addon and is not implemented.  Attribute names will only accept WChar types.  Any formatting should be done either in the data source view as a "Named Calculation" or in the source table/view on the relational source.

You do formatting where it makes the most sense to do it. You need to consider a myriad of conditions like storage, performance, consistency, ease-of-use, maintenance, extensibility, etc. We like to say format is a user concern and belongs in the presentation tier, and most times that may be true, but there are many, many cases where formatting elsewhere is either desirable or even required.

Answer 29

Can you make a suggestion at Connect and post the URL here? Thanks.

---

Kalman Toth SQL 2008 GRAND SLAM
Paperback: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012