Oracle PL/SQL动态转换任意格式数字为标准格式的实现求助

针对你需要处理的多种数字格式转换需求，我整理了一个能覆盖所有测试场景的解决方案，先看完整代码：

SELECT
    attribute2,
    -- 核心转换逻辑
    CASE
        -- 处理括号包裹的负数
        WHEN REGEXP_LIKE(input_num, '^\((.*)\)$') THEN
            TO_CHAR(
                -TO_NUMBER(
                    -- 统一处理千位分隔符与小数点
                    CASE
                        -- 同时存在逗号和点的情况，以最后出现的符号为小数点
                        WHEN REGEXP_COUNT(input_num, '[.,]') = 2 THEN
                            REPLACE(REPLACE(SUBSTR(input_num, 2, LENGTH(input_num)-2), 
                                CASE WHEN INSTR(input_num, '.') > INSTR(input_num, ',') THEN ',' ELSE '.' END, ''),
                                CASE WHEN INSTR(input_num, '.') > INSTR(input_num, ',') THEN '.' ELSE ',' END, '.')
                        -- 只有逗号的情况：判断是小数点还是千位分隔符
                        WHEN REGEXP_LIKE(input_num, ',') THEN
                            CASE WHEN REGEXP_LIKE(SUBSTR(input_num, 2, LENGTH(input_num)-2), ',\d{1,2}$') THEN
                                REPLACE(SUBSTR(input_num, 2, LENGTH(input_num)-2), ',', '.')
                            ELSE
                                REPLACE(SUBSTR(input_num, 2, LENGTH(input_num)-2), ',', '')
                            END
                        -- 只有点的情况：判断是小数点还是千位分隔符
                        WHEN REGEXP_LIKE(input_num, '\.') THEN
                            CASE WHEN REGEXP_LIKE(SUBSTR(input_num, 2, LENGTH(input_num)-2), '\.\d{1,2}$') THEN
                                SUBSTR(input_num, 2, LENGTH(input_num)-2)
                            ELSE
                                REPLACE(SUBSTR(input_num, 2, LENGTH(input_num)-2), '.', '')
                            END
                        -- 无分隔符的整数
                        ELSE
                            SUBSTR(input_num, 2, LENGTH(input_num)-2)
                    END
                ),
                'FM9999999999.00' -- 强制保留两位小数，移除前导空格
            )
        -- 处理正数
        ELSE
            TO_CHAR(
                TO_NUMBER(
                    CASE
                        WHEN REGEXP_COUNT(input_num, '[.,]') = 2 THEN
                            REPLACE(REPLACE(input_num, 
                                CASE WHEN INSTR(input_num, '.') > INSTR(input_num, ',') THEN ',' ELSE '.' END, ''),
                                CASE WHEN INSTR(input_num, '.') > INSTR(input_num, ',') THEN '.' ELSE ',' END, '.')
                        WHEN REGEXP_LIKE(input_num, ',') THEN
                            CASE WHEN REGEXP_LIKE(input_num, ',\d{1,2}$') THEN
                                REPLACE(input_num, ',', '.')
                            ELSE
                                REPLACE(input_num, ',', '')
                            END
                        WHEN REGEXP_LIKE(input_num, '\.') THEN
                            CASE WHEN REGEXP_LIKE(input_num, '\.\d{1,2}$') THEN
                                input_num
                            ELSE
                                REPLACE(input_num, '.', '')
                            END
                        ELSE
                            input_num
                    END
                ),
                'FM9999999999.00'
            )
    END AS formatted_amount
FROM TEST;

关键逻辑说明： #

• 负数识别：先匹配括号包裹的输入，提取内部内容后标记为负数，最终输出带-的格式
• 分隔符区分：
  • 同时存在,和.时，以最后出现的符号作为小数点，另一个作为千位分隔符移除
  • 单个符号时，判断符号后是否跟1-2位数字，是则作为小数点，否则作为千位分隔符移除
• 格式化输出：用FM9999999999.00格式确保强制保留两位小数，避免Oracle默认的前导空格

测试场景验证： #

输入(200.000,00) → 输出-200000.00
输入(200,000.00) → 输出-200000.00
输入(20000) → 输出-20000.00
输入1000000 → 输出1000000.00
输入(20,000.00) → 输出-20000.00
输入10,000,000 → 输出10000000.00
输入12.5 → 输出12.50
输入12,5 → 输出12.50

原有代码的问题： #

1. 未处理括号表示的负数场景
2. 正则限制了千位分隔符的固定格式（必须每三位一组），但无分隔符的整数无需额外限制
3. 未覆盖逗号作为小数点的情况（比如12,5）
4. 转换后未强制保留两位小数

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