SQL Server全文搜索仅返回单个值 如何获取多个匹配结果

SQL Server全文搜索原生支持返回多个匹配结果，仅返回单个值基本是检索写法有误，并非功能本身存在限制。

如果需要同时获取test、test1、test2这类多个匹配结果，可按以下方式实现：

• 拉取所有命中的匹配行
  不要使用单关键词等值匹配的写法，直接调用表值类全文检索函数CONTAINSTABLE/FREETEXTTABLE，在检索条件中用OR拼接多个目标词，或者用通配符匹配同前缀的所有目标词，函数会自动返回所有符合条件的匹配行，同时附带匹配度排名。
  示例代码：

  -- 示例1：逐个指定关键词，匹配content字段中包含test、test1、test2的行，按匹配度倒序返回
  SELECT t.*, ft.RANK AS match_score
  FROM your_business_table t
  INNER JOIN CONTAINSTABLE(your_business_table, content, N'test OR test1 OR test2') ft
      ON t.primary_key = ft.[KEY]
  ORDER BY ft.RANK DESC;
  
  -- 示例2：如果要匹配所有test开头的词，不需要逐个枚举，直接用前缀通配即可
  SELECT t.*, ft.RANK AS match_score
  FROM your_business_table t
  INNER JOIN CONTAINSTABLE(your_business_table, content, N'"test*"') ft
      ON t.primary_key = ft.[KEY]
  ORDER BY ft.RANK DESC;
  

• 提取行内所有命中的关键词片段
  如果需要的不是整行匹配结果，而是把字段值里每一个命中的关键词（比如同一段文本里同时出现的test、test1、test2）单独拆成结果返回，可以在全文检索筛中目标行的基础上，配合正则表值函数做二次拆分（SQL Server 2017及以上版本原生支持正则函数）。
  示例代码：

  SELECT 
      t.primary_key,
      matched_word.value AS hit_keyword
  FROM your_business_table t
  INNER JOIN CONTAINSTABLE(your_business_table, content, N'"test*"') ft
      ON t.primary_key = ft.[KEY]
  -- 逐行提取所有符合规则的匹配片段，g参数代表全局匹配，找到所有符合项才终止
  CROSS APPLY REGEXP_MATCHES(t.content, N'test[a-zA-Z0-9]*', 'g') AS matched_word;
  

注意：不要把全文检索条件写死为单个词的等值匹配，也不要用自定义标量函数包装检索结果，这两类写法都会导致最终只返回单个匹配值。

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