确定列上使用的MAX小数位数 [英] Determine MAX Decimal Scale Used on a Column

问题描述

在MS SQL中，我需要一种方法来确定某个十进制列的行所使用的最大比例.

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

例如Col1 Decimal(19,8)的小数位数为8，但是我需要知道是否实际上全部使用了8个，还是仅使用了5个，6个或7个.

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.

样本数据:

123.12345000
321.43210000
5255.12340000
5244.12345000

对于上述数据，我需要查询返回5或123.12345000或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.

推荐答案

虽然不漂亮，但我认为应该可以解决问题:

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

这篇关于确定列上使用的MAX小数位数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！