Postgres中like、rlike、similar to区别及TSQL兼容用法与选用规则问询

问题1：哪款运算符最接近TSQL中LIKE的功能特性 #

你遇到的语句执行结果差异，核心原因是PostgreSQL原生LIKE仅支持%（匹配任意长度任意字符）、_（匹配单个任意字符）两个通配符，你写的[0-9]会被识别为普通的方括号+字符组合，不会触发字符集匹配逻辑。
在你提到的三类运算符中，SIMILAR TO是最接近TSQL LIKE功能特性的选项：

• 它兼容%、_这两个LIKE体系的通用通配符
• 支持[]字符集匹配、|或逻辑、量词规则等扩展语法，和TSQL LIKE的扩展匹配能力对齐
  你给出的示例语句在PostgreSQL中改写为以下写法即可得到和TSQL一致的结果：

SELECT * FROM table WHERE colA SIMILAR TO '%[0-9]%[a-z]%'

如果你的TSQL环境使用的是大小写不敏感的排序规则，把字符集调整为[a-zA-Z]即可对齐效果。
另外补充两个同类运算符的差异供你参考：

• LIKE：仅支持基础通配符，大小写敏感，无扩展匹配能力，跨数据库兼容性最高
• ILIKE：和LIKE功能完全一致，唯一区别为默认大小写不敏感

问题2：PostgreSQL字符串匹配运算符选择通用原则 #

可以按照你的匹配需求复杂度、性能要求直接对应选择：

• 仅需基础模糊匹配（只有%、_通配符需求）：优先选LIKE/ILIKE，性能比正则类运算符更高，语法通用性最强，大小写敏感场景用LIKE，不敏感场景用ILIKE
• 需要简单扩展匹配规则（字符集、基础逻辑判断、少量量词），同时希望保留LIKE通配符使用习惯：选SIMILAR TO，适合从其他数据库迁移的存量SQL改造场景
• 需要复杂正则匹配逻辑：直接用PostgreSQL原生POSIX正则运算符~（大小写敏感）、~*（大小写不敏感），功能覆盖度远高于SIMILAR TO，语法和通用正则规则完全对齐，适合自定义复杂匹配规则的场景
• 大批量数据模糊匹配有性能要求：优先给匹配字段创建对应索引，前缀匹配场景用B树索引即可，全模糊匹配场景可以通过pg_trgm扩展创建GIN/GIST索引，性能远高于全表扫描

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