SQL实现匹配连字符(-)后含'John'字符串的记录查询方法

匹配规则 #

查询users表中name字段满足以下要求的记录：

• 字段值包含字符串John
• 至少有一个John出现在1个及以上连字符(-)之后
• 字符串开头、所有连字符之前出现的John不满足判定条件

判定样例 #

• 符合条件的记录值：
  • Anna - Pam - John
  • Anna - John
  • Anna - Pam - Mike - PhilJohn
  • John - John
• 不符合条件的记录值：
  • John - Anna - Pam
  • John

常见错误写法 #

错误SQL：

Select * from users where name LIKE '%John%';

问题：该写法会匹配所有任意位置包含John的记录，包含开头直接出现John、前面无连字符的不符合规则的记录。

正确写法（全SQL方言兼容） #

利用LIKE通配符按顺序匹配的特性，直接写为：

SELECT * FROM users WHERE name LIKE '%-%John%';

写法逻辑说明 #

• 第一个%：匹配连字符之前的任意长度内容（支持空值，覆盖-John这类开头就是连字符的场景）
• -：强制匹配必须存在的连字符，保证命中的John前面至少有一个连字符
• 第二个%：匹配连字符和John之间的任意长度内容（覆盖空格、其他名字字符，支持PhilJohn这类John不是独立单词的场景）
• John：匹配目标字符串
• 最后一个%：匹配John之后的任意长度内容

这个写法可以100%覆盖所有判定样例，不需要依赖正则函数，在MySQL、PostgreSQL、SQL Server、SQLite等所有支持标准SQL的数据库中都可以直接运行。

正则扩展写法（适用于复杂扩展匹配场景） #

如果使用的数据库支持正则匹配，可以用对应语法实现，逻辑和上述LIKE写法完全一致：

• MySQL写法：

  SELECT * FROM users WHERE name REGEXP '-.*John';
  

• PostgreSQL写法：

  SELECT * FROM users WHERE name ~ '-.*John';
  

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