Number formats in a view

Question

Post reply

Number formats in a view

Digs

SSCertifiable

Points: 7333
More actions
May 12, 2005 at 6:20 pm

#164494

I have a number like this
125113424.265161
I want it to look like =>
125,113,424
OR
$125,113,424.27
How to do this ?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

philcart SSC-Forever Points: 47794 More actions · Answer 1

Isn't this a presentation issue that should be defined on the client, not tying up database server resources?

--------------------
Colt 45 - the original point and click interface

Digs SSCertifiable Points: 7333 More actions · Answer 2

May be so, I was just wondering if one can do this ??

If so how ?

bersileus sacamay-126226 SSChasing Mays Points: 642 More actions · Answer 3

bersileus sacamay-126226

SSChasing Mays

Points: 642

May 13, 2005 at 2:08 am

#558625

have u tried UDT?

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 4

Why use a UDT? Are you saying he should create a string representation of numbers that have the required format and store that? That would be a very bad idea, since that means it would no longer be a number and would need other operations to be used as one.

Digs, you should not care so much how SQL Server stores data. Instead you should focus on presenting the data in the format you require, either by 'transforming' it in a query, or as Phil says do this on the client. After all, normally when a client asks for a number they are really asking for a number, probably because they want to add it to another number (or something). If you give them a string that looks like a number in a certain format they will need to convert that string back into a number to use it as a number.

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

Farrell Keough SSCoach Points: 17414 More actions · Answer 5

Go to your Tools ---> Options and start playing with those settings. I have my machine set that way because once I get into the billions of $, I cannot easily see what I am portraying. Be aware that this affects everything and I now have to do extra coding for integers [ CONVERT( varchar(10), IntegerField) AS IntegerField - you may need to add additional code to show a dollar sign - I have found no need to that]. Plus's and minus's to everything....

I wasn't born stupid - I had to study.

bersileus sacamay-126226 SSChasing Mays Points: 642 More actions · Answer 6

hi cris

>>May be so, I was just wondering if one can do this ??

yup,

but technically when considering work to be done

>>should focus on presenting the data in the format you require, either by 'transforming' it in a query, or as Phil says do this on the client

philcart SSC-Forever Points: 47794 More actions · Answer 7

Ok, if it "MUST" be done in T-SQL then here are a couple of methods to try,

With decimals

SELECT CONVERT(varchar(30), CONVERT(money, 1234567.890), 1)

Without decimals

SELECT SUBSTRING(CONVERT(varchar(30), CONVERT(money, 1234567.890), 1), 1
, CHARINDEX('.', CONVERT(varchar(30), CONVERT(money, 1234567.890), 1)) -1)

Now you can see that something like

Format$(field, "$#,##0.00")

is so much simpler

--------------------
Colt 45 - the original point and click interface