Determine MAX Decimal Scale Used on a Column

问题:

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.

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

回答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.

回答3:

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:

• https://www.postgresql.org/docs/9.6/static/sql-createcast.html
• https://www.postgresql.org/docs/9.6/static/functions-matching.html

回答4:

Note this will scan the entire table:

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