前提条件:
不能有空数据。
场景1:字符串比较
在做数值对比时涉及到负数值时,字符型转换数值型。
SELECT
CUTTIME,
ALUMINUM_LOSS_VALUE AS 字符型,
CAST(ALUMINUM_LOSS_VALUE AS INT) AS 整数型,
CAST(ALUMINUM_LOSS_VALUE AS DECIMAL(9,2)) AS 小数型
FROM
BSAMMSZZ00.T_DWD_FACT_ZZPQ_PROC_JRSLT
WHERE
ALUMINUM_LOSS_VALUE != ''
转换前 与 转换后
场景2:Code: -420, SQL State: 22018
字符数值中涉及到四位后有逗号标识符需要替换成空字符。
SELECT
CUTTIME ,
OXYGEN_END_CONVERTER_VALUE AS 字符型,
REPLACE(OXYGEN_END_CONVERTER_VALUE,',' ,'') AS 替换空,
CAST(REPLACE(OXYGEN_END_CONVERTER_VALUE,',' ,'') AS INT) AS 整数型,
CAST(REPLACE(OXYGEN_END_CONVERTER_VALUE,',' ,'') AS DECIMAL(9,2)) AS 小数型
FROM
BSAMMSZZ00.T_DWD_FACT_ZZPQ_PROC_JRSLT
WHERE
OXYGEN_END_CONVERTER_VALUE != ''