Formatting numbers with commas in TSQL

[David Sharp]

Once I've converted my floats to chars using STR, is there an easy way
to put commas in separating the thousands.

i.e. convert
53000000.12
to
53,000,000.12

I'm thinking I'll have to do it with a user defined function and the
various string functions myself but was wondering if anyone had an
easier way?

Cheers
Dave

[Greg D. Moore \(Strider\)]

"David Sharp" <da**@daveandcaz.freeserve.co.uk> wrote in message
news:ca*************************@posting.google.co m...

Once I've converted my floats to chars using STR, is there an easy way
to put commas in separating the thousands.

i.e. convert
53000000.12
to
53,000,000.12

I'm thinking I'll have to do it with a user defined function and the
various string functions myself but was wondering if anyone had an
easier way?
Yes, do it at your presentation layer, not at the DB layer.


Cheers
Dave

[Samuel Hon]

Hi Dave

You shouldnt really do any formatting of numbers in the database. It
should be done on the client.

Sam

da**@daveandcaz.freeserve.co.uk (David Sharp) wrote in message news:<ca*************************@posting.google.c om>...

Once I've converted my floats to chars using STR, is there an easy way
to put commas in separating the thousands.

i.e. convert
53000000.12
to
53,000,000.12

I'm thinking I'll have to do it with a user defined function and the
various string functions myself but was wondering if anyone had an
easier way?

Cheers
Dave

[Simon Hayes]

da**@daveandcaz.freeserve.co.uk (David Sharp) wrote in message news:<ca*************************@posting.google.c om>...

Once I've converted my floats to chars using STR, is there an easy way
to put commas in separating the thousands.

i.e. convert
53000000.12
to
53,000,000.12

I'm thinking I'll have to do it with a user defined function and the
various string functions myself but was wondering if anyone had an
easier way?

Cheers
Dave

You could do this TSQL by creating your own functions, but you should
probably do it in the front end application instead, if possible. The
format above is incorrect in Spain and Germany, for example - a client
application can retrieve locale information and format the output
accordingly much more easily than doing it on the server side.

Simon

[BenignVanilla]

"David Sharp" <da**@daveandcaz.freeserve.co.uk> wrote in message
news:ca*************************@posting.google.co m...

Once I've converted my floats to chars using STR, is there an easy way
to put commas in separating the thousands.

i.e. convert
53000000.12
to
53,000,000.12

I'm thinking I'll have to do it with a user defined function and the
various string functions myself but was wondering if anyone had an
easier way?

IMHO, this is a job for the Presentation Layer/GUI. I wouldn't store commas,
or format the numbers for me. I'd rather handle this on the client side so I
am not storing needless data (, and .) in the database, and so I can format
the data properly according to regional setting on the client side.
--
BV.
WebPorgmaster - www.IHeartMyPond.com
Work at Home, Save the Environment - www.amothersdream.com

[louis nguyen]

> Once I've converted my floats to chars using STR, is there an easy way

to put commas in separating the thousands.

i.e. convert
53000000.12
to
53,000,000.12

I'm thinking I'll have to do it with a user defined function and the
various string functions myself but was wondering if anyone had an
easier way?

Cheers
Dave

Hi Dave,

If you really want to do it in TSQL. Cast as Money. Then convert
using style 1. "convert(varchar,cast(myVar as money),1)" - Louis

[David Sharp]

Thanks to everyone who replied and of course who are absolutely
correct that this sort of data formatting should be done in a
presentation layer rather than the db.

Unfortunately in this specific case, the text is being generated to
annotate a simple calculation performed in a stored proc called by a
trigger and so doesn't have a presentation layer in which to do the
formatting.

Thanks Louis for highlighting a possible solution.

lo************@hotmail.com (louis nguyen) wrote in message news:<b0*************************@posting.google.c om>...

If you really want to do it in TSQL. Cast as Money. Then convert
using style 1. "convert(varchar,cast(myVar as money),1)" - Louis