PostgreSQL 按右侧第3位替换分隔符:欧式货币转英式格式
PostgreSQL 混用分隔符的货币格式统一转换实现
核心转换逻辑
解决这类欧式、英式货币格式混用的问题,核心是遵循货币格式的通用设计规则,跳出固定位置替换的思维:
- 字符串中最靠右的分隔符(逗号/点)一定是真实的小数点位置:千分位分隔符仅出现在小数点左侧,不会出现在小数点后方
- 识别到真实小数点位置后,将该位置的分隔符统一替换为英式格式使用的点
- 删除小数点左侧所有的逗号、点类千分位分隔符,消除多余的格式标记
- 无任何分隔符的纯数字字符串直接返回原数值即可
以上逻辑可完全覆盖所有给定的转换规则,同时兼容不同小数位长度的场景。
自定义PL/pgSQL转换函数
CREATE OR REPLACE FUNCTION unified_currency_to_num(raw_val text) RETURNS numeric LANGUAGE plpgsql AS $$ DECLARE -- 记录最靠右的分隔符位置(字符串索引从1开始计算) last_sep_pos int; processed_str text; BEGIN -- 空输入直接返回 IF raw_val IS NULL THEN RETURN NULL; END IF; -- 去除字符串两端的空白字符 raw_val := btrim(raw_val); -- 计算逗号、点的最后出现位置,取最大值作为真实小数点位置 last_sep_pos := GREATEST( STRPOS(raw_val, ','), STRPOS(raw_val, '.') ); -- 无分隔符的纯数字,直接转数值返回 IF last_sep_pos = 0 THEN RETURN raw_val::numeric; END IF; -- 将识别到的小数点位置的分隔符统一替换为点 processed_str := OVERLAY( raw_val PLACING '.' FROM last_sep_pos FOR 1 ); -- 拼接最终格式:删除左侧千分位分隔符 + 保留小数点 + 拼接右侧小数部分 processed_str := REGEXP_REPLACE(LEFT(processed_str, last_sep_pos - 1), '[,.]', '', 'g') || '.' || RIGHT(processed_str, LENGTH(processed_str) - last_sep_pos); -- 转换为数值返回 RETURN processed_str::numeric; EXCEPTION -- 非数值类脏数据直接返回NULL WHEN others THEN RETURN NULL; END; $$;
转换效果验证
执行以下测试语句,可完全匹配预期的转换规则:
SELECT unified_currency_to_num('11,200.00') AS case1, -- 输出11200.00 unified_currency_to_num('11.200,00') AS case2, -- 输出11200.00 unified_currency_to_num('58.20') AS case3, -- 输出58.20 unified_currency_to_num('58,20') AS case4, -- 输出58.20 unified_currency_to_num('2500') AS case5; -- 输出2500
常规right()、replace()方案的常见问题
之前直接使用right()、replace()的组合容易出现适配问题,核心原因是没有识别真实的小数点位置:
- 全局替换逻辑(如直接
replace(',', '.'))会把欧式格式中的千分位点、小数逗号全部转为点,导致出现多个小数点的非法格式 - 固定位置替换逻辑(如固定从右数第3位替换分隔符)无法适配不同小数位数的场景,比如两位小数的
58,20分隔符在倒数第2位,会被漏处理 - 上述自定义函数基于通用货币格式规则识别小数点位置,不需要固定适配小数位长度,兼容性更强
内容的提问来源于stack exchange,提问作者Solomon Raja
相关产品推荐
相关产品推荐

