Formating Numbers with Commas

[cleq...@nuvell.com]

I am pulling numbers from a SQl Table and adding some together and
doing other calculations. How do your format the numbers to insert a
comma and show thousands?

[Cat]

I am pulling several numbers from a SQL table, adding them and doing
various calculaitons. The numbers do not display a comma to separate
thousands. What is a way to format this?

[Simon Hayes]

Presentation and formatting are usually done in the client, not the
server. In this case, for example, many countries do not use a comma
for separating thousands, so your client application can check the
user's locale and apply the correct formatting.

Simon

[jennif...@hotmail.com]

There's no easy way to do this. For some reason I did need output like
this and wasn't able to use a front end to do the formatting, so I made
my own function.

Use as so:

SELECT dbo.Format_Number(513434512.2344)

Output is $513,434,512.23

Yes, it rounds and adds a dollar sign. But you can change it around.
:)

HTH,
Jennifer

CREATE FUNCTION Format_Number (@N decimal(18,2))
RETURNS nVarChar(30)

AS

BEGIN

Declare @NRnd Decimal(18,2)
Declare @Dollar nVarChar(30)
Declare @Dollar2 nVarChar(30)
Declare @L int
Declare @A int
Declare @B int
Declare @C int
Declare @Cents nvarchar(20)
Declare @NC nvarchar(30)

Set @NC = Cast(@N as Nvarchar(30))

Set @NRnd = Round(@N, 0, 1)
Set @Dollar2 = ''
Set @Dollar = Cast(@NRnd as NvarChar(30))
Set @Dollar = Substring(@Dollar,1, Len(@Dollar) - 3)

Set @C = PATINDEX('%.%',@NC)
Set @Cents = Substring(@NC, @C, 3)
Set @L = Len(@Dollar)
Set @A = @L/3

Set @B = 3
While @A >= 0
Begin
Set @Dollar2 = Substring(@Dollar,@L - @B + 1,3) + ',' + @Dollar2
Set @B = @B + 3
Set @A = @A - 1
End
If Left(@Dollar2,1) = ','
Set @Dollar2 = Substring(@Dollar2, 2, Len(@Dollar2))

Return '$' + Substring(@Dollar2,1, Len(@Dollar2)-1) + @Cents
END

[--CELKO--]

Display formatting is always done In the front end, of course This is
the basic idea of a tiered architecture, which is more fundamental than
just SQL.

[--CELKO--]

>> For some reason I did need output like this and wasn't able to use a
front end to do the formatting, so I made my own function. <<

Since this is a fundamental violation of software engineering
prtinciples, might you share with us WHAT that reason was? It is worth
a paper in a journal.

[jennif...@hotmail.com]

It was a totally stupid reason, of course. :) My boss wanted an email
output of a query emailed to him on a daily basis, so I set up a job to
do that. And then he came back and said, it sure would be nice if
those dollar amounts looked like dollars, and could the output be
changed. So being completely new and straight out of school I did as
asked.

Let me know how that paper comes out, will you? ;)

[Erland Sommarskog]

--CELKO-- (jcel...@earthlink.net) writes:
> Since this is a fundamental violation of software engineering
> prtinciples, might you share with us WHAT that reason was? It is worth
> a paper in a journal.

The world is not always as ideal as you may want to be. There are probably
tons of business reports out there that are run from no other front end
than Query Analyzer, or similar tool. For some reason, someone started to
do it in QA, probably because it was a little urgent, and not possible to
pack into something better. Then that temporary hack became permaent etc.
Until one day, the requirements goes beyond what is really healthy to do
in SQL.

Anoher reason could be that the front-end tool is hopelessly difficult
to use...

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp