Informix迁移PostgreSQL：MATCHES运算符替代方案咨询

核心逻辑对应关系 #

Informix的MATCHES运算符同时支持通配符（*代表任意长度任意字符）和字符范围匹配，PostgreSQL中没有完全等价的运算符，但可以通过SIMILAR TO、正则表达式运算符~或LIKE分场景实现。

疑问1：实现MATCHES '*[Q-T]'（匹配以Q-T结尾的内容） #

Informix的*[Q-T]表示任意前缀 + 以Q到T之间的单个字符结尾，PostgreSQL有两种直接实现方式：

• 使用SIMILAR TO：语法和Informix最贴近，直接将*替换为%即可，SIMILAR TO原生支持[]字符范围语法：

  cas.last_name SIMILAR TO '%[Q-T]'
  

• 使用正则表达式运算符~：用.*匹配任意前缀，$锚定字符串结尾，更灵活：

  cas.last_name ~ '.*[Q-T]$'
  

  若需要大小写不敏感匹配，可改用~*：cas.last_name ~* '.*[q-t]$'

疑问2：%是否为合法通配符，能否直接替换所有* #

PostgreSQL的LIKE运算符中，%是合法通配符，功能和Informix的*完全一致（匹配任意长度的任意字符，包括空字符串），但不能无条件替换所有场景：

• 当模式仅包含*（如*2972*、*54123、*），可以直接替换为%用LIKE，例如：

  -- Informix: a.recog_id MATCHES '*2972*'
  a.recog_id LIKE '%2972%'
  
  -- Informix: cp.number MATCHES '*54123'
  cp.number LIKE '%54123'
  
  -- Informix: cas.county MATCHES '*'（匹配所有内容，包括空字符串）
  cas.county LIKE '%'
  

• 当模式包含[]字符范围（如[Uu]*、*[Q-T]），不能用LIKE：因为LIKE不将[]识别为字符范围，LIKE '[Uu]%'会匹配以[、U、u、]开头的字符串，而非以U或u开头。此时必须用SIMILAR TO或正则表达式：

  -- Informix: cas.frst_name MATCHES '[Uu]*'（匹配以U或u开头的任意内容）
  cas.frst_name SIMILAR TO '[Uu]%'
  -- 正则表达式版本
  cas.frst_name ~ '^[Uu].*'
  

完整转换后的查询示例 #

基于SIMILAR TO的版本（最贴近原Informix语法） #

SELECT DISTINCT ca.cust_id, a.acct_id 
FROM cust_acct ca 
JOIN acct a on a.acct_id = ca.acct_id  
JOIN cust_address cas on ca.cust_id = cas.cust_id 
JOIN cust c on ca.cust_id = c.cust_id 
JOIN phone cp on c.cust_id = cp.cust_id 
WHERE a.recog_id SIMILAR TO '%2972%'  
AND cas.last_name SIMILAR TO '%[Q-T]' 
AND cas.frst_name SIMILAR TO '[Uu]%' 
AND cas.city SIMILAR TO '%X'
AND cas.county SIMILAR TO '%'
AND cp.number SIMILAR TO '%54123'

基于正则表达式的版本 #

SELECT DISTINCT ca.cust_id, a.acct_id 
FROM cust_acct ca 
JOIN acct a on a.acct_id = ca.acct_id  
JOIN cust_address cas on ca.cust_id = cas.cust_id 
JOIN cust c on ca.cust_id = c.cust_id 
JOIN phone cp on c.cust_id = cp.cust_id 
WHERE a.recog_id ~ '.*2972.*'  
AND cas.last_name ~ '.*[Q-T]$' 
AND cas.frst_name ~ '^[Uu].*' 
AND cas.city ~ '.*X$'
AND cas.county ~ '.*' -- 等价于匹配所有内容，可简化为1=1
AND cp.number ~ '.*54123$'

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