467,166 Members | 1,076 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,166 developers. It's quick & easy.

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
Jul 20 '05 #1
  • viewed: 44434
Share:
6 Replies

"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
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
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

"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
> 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
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.

Similar topics

9 posts views Thread by Rowan Chapman | last post: by
reply views Thread by Mike | last post: by
7 posts views Thread by Christopher Robin | last post: by
4 posts views Thread by John Sutor | last post: by
2 posts views Thread by Smiley | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.