如何将Oracle SQL中的instr与regexp_like写法转换为Postgres语法

原代码逻辑说明 #

这段Oracle代码的核心作用是校验药品名称字段mname满足两个条件：

• 转大写后的名称中，包含药品编码字段dd.drug的字符串内容
• 匹配的药品名称符合独立词汇边界要求，不会作为其他更长英文单词的子串被误命中（比如不会把“aspirin”匹配到“gasprina”这类词中间）

等价迁移语法 #

按原逻辑1:1无差异替换的可直接运行代码如下：

strpos(upper(mname), dd.drug) > 0
and regexp_like(
  upper(mname),
  '(^|[^A-Z]+)' || dd.drug || '([^A-Z]+|$)'
)

迁移细节说明 #

• Oracle instr() 函数的替换：Postgres 早期版本无原生兼容的instr实现，使用strpos() 可完全复现其行为：该函数返回子串在源字符串中首次出现的位置，未找到返回0，和Oracle instr默认从起始位置检索首次匹配的逻辑完全一致。如果使用的是Postgres 14及以上版本，也可以直接保留原写法instr(upper(mname), dd.drug) > 0，无需调整。
• Oracle regexp_like() 函数的替换：Postgres 10及以上版本已原生支持和Oracle语法完全一致的regexp_like() 函数，直接沿用原正则拼接逻辑即可。如果使用的是低于10的老旧版本Postgres，可以替换为Postgres原生正则匹配操作符~，调整后的写法为：

  strpos(upper(mname), dd.drug) > 0
  and upper(mname) ~ ('(^|[^A-Z]+)' || dd.drug || '([^A-Z]+|$)')
  

• 迁移时需保证行为对齐：不要主动修改正则匹配规则（比如额外给dd.drug加大写转换、放宽单词边界校验逻辑），避免出现和原Oracle库查询结果不一致的问题。原代码里第一个instr判断虽然和第二个正则匹配存在逻辑冗余（正则命中时必然满足包含子串的要求），但迁移时建议保留原逻辑结构，不要擅自删减。

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