如何确定decimal类型数值的有效小数位数（ERP至SAP迁移场景）

问题描述 #

我需要确定decimal类型数值的有效小数位数——比如0.64的有效小数位数是2位，0.604是3位。尝试从右侧查找末尾零的方法没成功，最终要通过这个位数计算对应的10的幂次，把小数转为整数后分别存入两个字段，用于旧ERP系统到SAP的迁移。我尝试了以下SQL代码：

declare @val decimal(10,6),
        @var varchar(50),
        @point int,
        @zero int,
        @calc int,
        @UMREZ int,
        @UMREN int

set @val = 0.604 
set @var = trim(str(@val, 10, 10))
set @point = cast(CHARINDEX('.', @var) as int)
set @zero = cast(right(CHARINDEX('0', @var, @point), 8) as int)
set @calc = @zero - @point - 1

set @umrez = case when @calc = 0 then 1 
                  when @calc = 1 then 10 
                  when @calc = 2 then 100
                  when @calc = 3 then 1000
                  when @calc = 4 then 10000
                  when @calc = 5 then 100000
                  when @calc = 6 then 1000000
         end
set @UMREN = @val * @UMREZ

select @var, @point, @zero, @calc, @UMREZ, @UMREN

问题分析 #

当前代码的核心错误是：CHARINDEX('0', @var, @point)是从小数点后第一个0的位置开始查找，而非找末尾的连续无效零，导致计算出的有效小数位数完全错误（比如0.604里的0是中间有效数字，不该被当作末尾零处理）。

正确实现方案 #

优化后的SQL代码 #

DECLARE @val DECIMAL(10,6),
        @str_val VARCHAR(50),
        @decimal_part VARCHAR(50),
        @scale INT,
        @UMREZ INT,
        @UMREN BIGINT -- 用BIGINT避免数值溢出

-- 测试用例：可替换为0.64、0.1200、1.0等
SET @val = 0.604

-- 去除小数部分末尾的无效零
SET @str_val = TRIM(REPLACE(RTRIM(REPLACE(CONVERT(VARCHAR(50), @val), '0', ' ')), ' ', '0'))

-- 提取小数部分
IF CHARINDEX('.', @str_val) > 0
    SET @decimal_part = SUBSTRING(@str_val, CHARINDEX('.', @str_val) + 1, LEN(@str_val))
ELSE
    SET @decimal_part = ''

-- 计算有效小数位数
SET @scale = LEN(@decimal_part)

-- 生成10的对应幂次
SET @UMREZ = CASE @scale
                WHEN 0 THEN 1
                ELSE POWER(10, @scale)
             END

-- 转换为整数
SET @UMREN = CAST(@val * @UMREZ AS BIGINT)

-- 输出结果
SELECT 
    @val AS 原始数值,
    @scale AS 有效小数位数,
    @UMREZ AS 转换乘数,
    @UMREN AS 转换后的整数

代码说明 #

1. 去除末尾无效零：利用REPLACE把0换成空格，RTRIM去掉末尾空格，再换回0，精准去除小数部分末尾的无效零
2. 提取小数部分：通过CHARINDEX定位小数点，分割出纯小数部分
3. 计算幂次：用POWER函数直接生成对应10的幂次，替代繁琐的CASE判断
4. 避免溢出：用BIGINT存储转换后的整数，防止小数位数较多时出现数值溢出

测试验证 #

内容的提问来源于stack exchange，提问作者Andrea