Query number of decimal places in a sql server resultset

Question

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

Answer 1

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.

Answer 2

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?