Formatting numbers with commas in TSQL

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"David Sharp" <dave@.daveandcaz.freeserve.co.uk> wrote in message
news:ca434844.0312150306.34760a0@.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|||Hi Dave

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

Sam

dave@.daveandcaz.freeserve.co.uk (David Sharp) wrote in message news:<ca434844.0312150306.34760a0@.posting.google.com>...
> 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|||dave@.daveandcaz.freeserve.co.uk (David Sharp) wrote in message news:<ca434844.0312150306.34760a0@.posting.google.com>...
> 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|||"David Sharp" <dave@.daveandcaz.freeserve.co.uk> wrote in message
news:ca434844.0312150306.34760a0@.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|||> 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|||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.

louisducnguyen@.hotmail.com (louis nguyen) wrote in message news:<b0e9d53.0312151608.7c1f93f2@.posting.google.com>...
> If you really want to do it in TSQL. Cast as Money. Then convert
> using style 1. "convert(varchar,cast(myVar as money),1)" - Louissql