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.

score 10 · Accepted Answer · answered Jan 23 '12 at 20:34

10

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

answered Jan 23 '12 at 20:34

Michael Fredrickson

36,839
5
92
109

Not pretty, but Brilliant. Works like a charm. Just added the MAX(), and filtered out 0.00000000. Adding MAX() gave me: SELECT MAX(9 - PATINDEX('%[1-9]%', REVERSE(Col1))) – Bo Flexson Jan 23 '12 at 20:47

score 8 · Answer 2 · edited May 23 '17 at 12:00

8

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:

As the argument of REVERSE, Col1 is converted to a string.
The bigint is cast as a string before being used as the argument of LEN.

edited May 23 '17 at 12:00

Community

1
1

answered Jan 23 '12 at 21:22

Andriy M

76,112
17
94
154

You helped find a solution to this one: http://stackoverflow.com/a/14715318/114029 I had to go even further and used this guy's post to get there: http://connectsql.blogspot.com.br/2011/04/normal-0-microsoftinternetexplorer4.html It's interesting how one gets to a solution building upon scattered pieces of info here and there... :D – Leniel Maccaferri Feb 05 '13 at 21:07

Bian Jiaping · Answer 3 · 2016-11-22T14:37:37.097

SELECT
    MAX(CHAR_LENGTH(
        SUBSTRING(column_name::text FROM '\.(\d*?)0*$')
    )) AS max_scale
FROM table_name;

*? is the non-greedy version of *, so \d*? catches all digits after the decimal point except trailing zeros.

The pattern contains a pair of parentheses, so the portion of the text that matched the first parenthesized subexpression (that is \d*?) is returned.

References:

score -1 · Answer 4 · answered Dec 08 '16 at 22:38

-1

Note this will scan the entire table:

SELECT TOP 1 [Col1] 
FROM [Table]
ORDER BY LEN(PARSENAME(CAST([Col1] AS VARCHAR(40)), 1)) DESC

answered Dec 08 '16 at 22:38

8kb

10,956
7
38
50

Determine MAX Decimal Scale Used on a Column

4 Answers4

Linked