如何使用regexp_replace清洗数据集 去除空格后内容并规范数值格式

实现方案 #

清洗规则梳理 #

通过两次正则替换即可实现需求，对应两条核心处理逻辑：

1. 截断无效后缀：从左到右扫描字符串，只保留第一个非数字/非小数点字符之前、所有由数字和小数点组成的内容，同时丢弃开头的非数字/非小数点前缀（如示例中的$）
2. 合并多余小数点：保留内容中第一个出现的小数点，删除其余所有小数点

可用SQL代码 #

SELECT CAST(
    REGEXP_REPLACE(
        REGEXP_REPLACE(你的字段名, '^[^0-9.]*([0-9.]*).*$', '\1'),
        '^([^.]*\.)|\.', '\1'
    ) AS DECIMAL(16,2)
) FROM 你的表名;

测试验证 #

对所有示例输入的处理结果完全符合预期：

• $14..89 → 14.89
• 30 0 → 30.00（数值等同于30，可根据需要调整输出格式）
• 40 - → 40.00
• 56 e → 56.00
• 37.5 → 37.50
• 0.25 → 0.25
• 16.8.9 → 16.89

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