By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,180 Members | 1,016 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,180 IT Pros & Developers. It's quick & easy.

Formatting numbers with commas in TSQL

P: n/a
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
Jul 20 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a

"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

Jul 20 '05 #2

P: n/a
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

Jul 20 '05 #3

P: n/a
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
Jul 20 '05 #4

P: n/a

"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
Jul 20 '05 #5

P: n/a
> 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
Jul 20 '05 #6

P: n/a
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

Jul 20 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.