使用正则表达式解析并拆分含货币与数值的数据库字段

针对你需要拆分数据库中「货币+数值」格式列（还要处理其中一项缺失的特殊场景），我整理了一套适配所有情况的SQL方案，先理清楚思路再上代码：

需求回顾 #

你的列存在三种格式：

• 完整格式：'USD 100000000.00'（货币代码+数值，空格分隔）
• 仅数值：' 100000000.00'（开头带空格，无货币）
• 仅货币：'USD '（结尾带空格，无数值）

适配所有场景的SQL方案 #

从你给出的to_number和dual能看出来是Oracle环境，核心思路是先统一处理首尾空格，再分别匹配货币和数值的特征：

SELECT 
    -- 提取货币列：仅当字符串包含字母时，取开头的字母片段
    CASE 
        WHEN REGEXP_LIKE(your_column, '[A-Za-z]') THEN REGEXP_SUBSTR(TRIM(your_column), '^[A-Za-z]+')
        ELSE NULL
    END AS currency,
    -- 提取数值列：仅当字符串包含数字/小数点时，取结尾的数字片段并转成数值类型
    TO_NUMBER(
        CASE 
            WHEN REGEXP_LIKE(your_column, '[0-9.]') THEN REGEXP_SUBSTR(TRIM(your_column), '[0-9.]+$')
            ELSE NULL
        END
    ) AS value
FROM your_table;

方案说明 #

1. TRIM(your_column)：先去掉字符串首尾的空格，把'USD '转成'USD'，' 100000000.00'转成'100000000.00'，统一后续匹配规则
2. 货币提取逻辑：用REGEXP_LIKE判断是否存在字母，再用REGEXP_SUBSTR取开头的连续字母（匹配货币代码），没有字母就返回空
3. 数值提取逻辑：用REGEXP_LIKE判断是否存在数字或小数点，再用REGEXP_SUBSTR取结尾的连续数字/小数点，转成NUMBER类型，没有就返回空

测试验证 #

针对你给出的测试场景：

• 输入'USD '：currency返回USD，value返回空（符合预期）
• 输入' 100000000'：currency返回空，value返回100000000（符合预期）
• 输入'USD 100000000.00'：currency返回USD，value返回100000000.00（符合预期）

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