views:

45

answers:

2

I have a table which contains a field of type numeric(28,10). I would like to display the records with a monospaced font, and a matching number of decimal places so that they line up when right aligned.

Is there any way to figure out the maximum number of decimal places that can be found in a given result set so that I can format accordingly as I display each record?

Edit: sorry, I should have been clearer ... if the result set contains numbers with only 3 decimal places, then all of the numbers should have only 3 decimal places (padded with zeroes).

A: 

Where do you want to display the results? Query Analyzer? In an application?

You can either

 a) format the column to have a
    finite number (known in advance) of
    digits to the right of the decimal
    point, truncating at that position;
    this is the typical practice or

 b)    read through all of the rows in the
    resultset to determine the value
    with the greatest number of digits
    to the right of the decimal point 
    (casting to string, splitting the
    value using the decimal point as
    delimiter, and getting the length of
    the decimal-fraction string)

If for some reason option a) is unacceptable then you'd have do do b) procedurally, either server-side in a stored procedure or client-side in your client program.

Tim
yeah, I was trying to avoid the string handling approach. and unfortunately capping the precision is not an option as you suggested.
Joel Martinez
+1  A: 

The monospaced font is entirely a presentation issue...

I don't see your need for right alignment when I test:

CREATE TABLE [dbo].[Table_1](
  [num] [numeric](28, 10) NOT NULL
)

INSERT INTO [example].[dbo].[Table_1] VALUES (1.1234567890);
INSERT INTO [example].[dbo].[Table_1] VALUES (1.123456789);
INSERT INTO [example].[dbo].[Table_1] VALUES (1.1234567);

SELECT [num]
  FROM [example].[dbo].[Table_1]

...returns:

num
---------------
1.1234567890
1.1234567890
1.1234567000

So the question is--what are you trying to do that isn't giving you the output you desire?

OMG Ponies
sorry, I should have been clear ... I don't want to go all the way out to 10 decimal places if I don't have to. A better example is 0.1, 0.12, 0.123 should display 0.100, 0.120, 0.123
Joel Martinez
@Joel Martinez: Then how do you intend to deal with numbers that do have 10 decimal places?!
OMG Ponies
If there's a number in the resultset that has 10 decimal places, then I expect to display *all rows* as having 10 decimal places. I want to show no more decimal places than is absolutely necessary ... but if the data necessitates it then I'd display them. The reason I want to do this is because I want the user to be able to scan the table and quickly be able to compare numbers. To do that the decimal places have to be lined up ... and no I don't want to display each part in a separate column ;-)
Joel Martinez
So the query needs to know the what the amount of precision to show based on the one with the most? You're guaranteeing two passes over the data, SQL or otherwise. I think you'd be better off showing the ten decimal places, but good luck!
OMG Ponies
ok, fair enough :-) I was hoping that someone would have some obvious solution that I hadn't thought of before, but it looks like I won't be able to avoid that. Thanks for verifying!
Joel Martinez