Hive中VARCHAR(7)转DECIMAL(7,5)返回NULL的解决方法

问题原因 #

直接执行CAST(column_A AS DECIMAL(7,5))返回NULL，是因为原字符串对应的整数值（如0050000对应50000）超出了DECIMAL(7,5)的取值范围：

• DECIMAL(7,5)表示总共有7位有效数字，其中5位是小数位，因此整数部分最多只能有2位（7-5=2），最大值为99.99999。
• 50000作为整数远大于99，导致转换时溢出，返回NULL。

解决方案 #

方案1：数值缩放转换 #

先将字符串转为大整数类型（BIGINT），再通过除法缩放到目标量级，最后转为DECIMAL(7,5)：

SELECT CAST(CAST(column_A AS BIGINT) / 1000000.0 AS DECIMAL(7,5)) AS converted_column
FROM table_name;

测试结果：

• 输入0050000 → 转换后得到0.05000
• 输入0100000 → 转换后得到0.10000
• 输入0005000 → 转换后得到0.00500

方案2：字符串拼接转换 #

如果需要严格按固定位置拆分数字插入小数点，可通过字符串函数拼接后转换（以0050000取第2-6位作为小数部分为例）：

SELECT CAST(CONCAT('0.', SUBSTR(column_A, 2, 5)) AS DECIMAL(7,5)) AS converted_column
FROM table_name;

注：可根据实际数据的数值规则，调整SUBSTR的截取参数。

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