Alex Rivera | Logout

Determine MAX Decimal Scale Used on a Column

Asked 2012-01-23T20:26:37.583
15

In MS SQL, I need a approach to determine the largest scale being used by the rows for a certain decimal column.

For example Col1 Decimal(19,8) has a scale of 8, but I need to know if all 8 are actually being used, or if only 5, 6, or 7 are being used.

Sample Data:

123.12345000
321.43210000
5255.12340000
5244.12345000

For the data above, I'd need the query to either return 5, or 123.12345000 or 5244.12345000.

I'm not concerned about performance, I'm sure a full table scan will be in order, I just need to run the query once.

sql sql-server t-sql decimal precision
Edit
Report

1 Answer

7

I like @Michael Fredrickson's answer better and am only posting this as an alternative for specific cases where the actual scale is unknown but is certain to be no more than 18:

SELECT LEN(CAST(CAST(REVERSE(Col1) AS float) AS bigint))

Please note that, although there are two explicit CAST calls here, the query actually performs two more implicit conversions:

  1. As the argument of REVERSE, Col1 is converted to a string.

  2. The bigint is cast as a string before being used as the argument of LEN.

answered 2012-01-23T21:22:02.960

Your Answer