如何在SQL表连接中实现双向子字符串匹配？

看起来你需要的是双向的子串包含匹配——不是简单的整个字段互相包含，而是两个字段之间存在重叠的有效子串（比如你例子里的「my name is」）。根据你的数据和需求，这里有两种可行的方案：

方案1：针对STRUCTURES字段的分隔符处理 #

如果STRUCTURES表的structure字段是用/分隔的多个独立短语，我们可以先把这些短语拆分成单独的行，再和DIALOG表做模糊匹配，这样就能精准匹配到包含共同短语的记录：

SELECT d.string, s.structure
FROM dialog d
-- 先把STRUCTURES的structure按" / "分割成单个片段
JOIN (
  SELECT 
    structure, 
    unnest(string_to_array(structure, ' / ')) AS structure_fragment
  FROM structures
) split_structs
  -- 检查DIALOG的string是否包含拆分后的某个片段（不区分大小写）
  ON d.string ILIKE '%' || split_structs.structure_fragment || '%'
-- 关联回原STRUCTURES表获取完整的structure值
JOIN structures s ON s.structure = split_structs.structure
-- 去重，避免同一个匹配被多次返回
GROUP BY d.string, s.structure;

这个查询会把his name is / my name is拆成his name is和my name is两个片段，然后发现my name is存在于Hi, my name is dan中，从而返回你期望的匹配结果。

方案2：基于 trigram 的通用双向子串匹配 #

如果你的场景不局限于/分隔的短语，而是需要任意两个字符串只要有足够长度的共同子串就匹配，可以用PostgreSQL的pg_trgm扩展（专门用于字符串相似度匹配）：

首先需要启用扩展（只需执行一次）：

CREATE EXTENSION IF NOT EXISTS pg_trgm;

然后执行查询：

SELECT d.string, s.structure
FROM dialog d
JOIN structures s 
  -- 使用trigram相似度操作符%，默认阈值0.3，可调整
  ON d.string % s.structure
-- 可选：手动调整相似度阈值，过滤掉匹配度太低的结果
WHERE similarity(d.string, s.structure) > 0.2;

这个方法会自动识别两个字符串的共同子串，你的例子中两个字符串有很长的重叠部分，相似度会远高于阈值，所以会被匹配到。

为什么你之前的ILike尝试没生效？ #

你之前可能写了类似ON d.string ILIKE '%'||s.structure||'%' OR s.structure ILIKE '%'||d.string||'%'的条件，但这是检查整个字段是否被包含在另一个字段中，而你的例子里两个字段都不包含对方的完整内容，所以匹配不上。上面的两种方案都是针对子串重叠的场景设计的，刚好解决你的问题。

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