Oracle SQL中TO_CHAR格式化问题：ROUND后输出异常需修正

核心问题 #

你遇到的####%异常输出，根源在于**Other1是字符串类型**，直接用它做数值大小比较时，Oracle会按字符串字典序（字符ASCII码顺序）而非数值大小判断，导致CASE分支选择错误，最终使用了不匹配的格式模型，当ROUND后的数值位数超出格式指定的整数位时，就会显示####。

比如字符串'9.965091...'和'10'比较时，首字符'9'的ASCII码大于'1'，所以字符串比较结果是'9.965...' > '10'，但实际数值是9.965 < 10，这就导致程序进入了错误的分支，使用了不适合的格式。

修正后的SQL代码 #

解决思路是先将Other1转换为数值类型，再进行比较和格式化操作：

SELECT
    CASE    
        WHEN TO_NUMBER(Other1) >= 10 AND TO_NUMBER(Other1) < 100 THEN TO_CHAR(ROUND(TO_NUMBER(Other1), 1), 'fm00.0') || '%'
        WHEN TO_NUMBER(Other1) < 10 THEN TO_CHAR(ROUND(TO_NUMBER(Other1), 1), 'fm0.0') || '%'
        WHEN TO_NUMBER(Other1) >= 100 THEN TO_CHAR(ROUND(TO_NUMBER(Other1), 1), 'fm000.0') || '%'
        ELSE '5' 
    END AS Other2
FROM test;

优化版本（避免重复转换） #

可以用CTE先统一转换为数值，让代码更简洁：

WITH test_num AS (
    SELECT 
        Total,
        Other,
        TO_NUMBER(Other1) AS Other1_num
    FROM test
)
SELECT
    CASE    
        WHEN Other1_num >= 10 AND Other1_num < 100 THEN TO_CHAR(ROUND(Other1_num, 1), 'fm00.0') || '%'
        WHEN Other1_num < 10 THEN TO_CHAR(ROUND(Other1_num, 1), 'fm0.0') || '%'
        WHEN Other1_num >= 100 THEN TO_CHAR(ROUND(Other1_num, 1), 'fm000.0') || '%'
        ELSE '5' 
    END AS Other2
FROM test_num;

效果验证 #

运行修正后的代码，输出会符合你的期望：

OTHER2
3.0%
10.0%
119.9%
9.5%

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