Determine MAX Decimal Scale Used on a Column

Question

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.

Answer 1

Not pretty, but I think it should do the trick:

-- Find the first non-zero character in the reversed string...
-- And then subtract from the scale of the decimal + 1.
SELECT 9 - PATINDEX('%[1-9]%', REVERSE(Col1))

Answer 2

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.