PostgreSQL基于正则实现两列值语义一致性比对的更新查询问题
PostgreSQL 语义一致判定的正确更新逻辑
原查询失效的核心原因
- 匹配逻辑不支持分隔符变形:原查询用
col1 like '%col2%'做原始字符串的包含判断,完全不考虑col1、col2中编号的分隔符可能存在多横杠、点号替代横杠等变形,哪怕两边数字序列完全一致,只要原始字符串有排版差异就会被判定为不匹配 - 缺少前置校验环节:没有先对col2的格式合规性做校验,也没有将两边的编号统一成无排版差异的纯数字序列再比对
- 逻辑覆盖不全:原查询仅设计了“不匹配则标记Not the same meaning”的分支,完全没有实现“语义一致则标记Same meaning”的赋值逻辑,所有满足子查询条件的行只会被统一标记为不一致,最终出现全量错标的问题
核心判定逻辑拆解
按照需求规则,语义一致的本质是忽略编号中的分隔符排版差异,仅比对数字序列是否完全相同,判断流程为:
- 先校验col2是否严格匹配标准正则
\d{3,6}-\d{3}-\d{2},不满足格式的直接判定为不一致 - 分别将col1中提取到的合规编号、col2本身做全量非数字字符移除,得到不受排版影响的纯数字序列
- 比对两个纯数字序列是否完全相等,相等则标记
Same meaning,否则标记Not the same meaning
可直接执行的正确查询
基础版本(适配col1仅含单条编号的场景)
UPDATE my_table SET result = CASE WHEN col2 !~ '\d{3,6}-\d{3}-\d{2}' THEN 'Not the same meaning' WHEN regexp_replace(col1, '[^0-9]', '', 'g') = regexp_replace(col2, '[^0-9]', '', 'g') THEN 'Same meaning' ELSE 'Not the same meaning' END;
- 函数说明:
regexp_replace(字段, '[^0-9]', '', 'g')会将字段内所有非数字字符全局替换为空,实现分隔符无关的纯数字提取 - 适配示例场景验证:
- col1为
teste 1452-251-99 azert、col2为1452-251-99:提取后纯数字均为145225199,标记一致 - col1为
teste 1225-71-45、col2为1225--71.45:提取后纯数字均为12257145,标记一致 - col1为
teste 1288-91-75、col2为1225--71.45:提取后纯数字分别为12889175、12257145,不相等,标记不一致
- col1为
优化版本(适配col1含无关冗余数字的场景)
如果col1文本中除了目标编号外,还夹杂其他无关数字(如前后附带的电话号码、序号),需要先精准抠出符合格式的编号片段再比对,避免无关数字干扰匹配结果:
UPDATE my_table SET result = CASE WHEN col2 !~ '\d{3,6}-\d{3}-\d{2}' THEN 'Not the same meaning' -- 先从col1中匹配符合结构的编号片段,匹配不到则返回空串 WHEN regexp_replace( COALESCE(substring(col1 from '\d{3,6}\D+\d{2,3}\D+\d{2}'), ''), '[^0-9]', '', 'g' ) = regexp_replace(col2, '[^0-9]', '', 'g') THEN 'Same meaning' ELSE 'Not the same meaning' END;
- 匹配正则说明:
\d{3,6}\D+\d{2,3}\D+\d{2}可灵活匹配所有带变形分隔符的编号结构,支持多横杠、点号替代横杠的场景,避免硬编码横杠导致的提取失败
内容的提问来源于stack exchange,提问作者khelili miliana
相关产品推荐
相关产品推荐

