TSQL如何查询包含逗号分隔列表中所有关键字的行

适用场景 #

给定逗号分隔的关键词字符串，查询指定列同时包含所有关键词（顺序不限）的所有数据行。

实现方案 #

方案1：SQL Server 2016及以上版本（支持STRING_SPLIT内置函数） #

这是最简单的实现方式，直接调用系统拆分函数处理关键词：

-- 定义输入的查询关键词
DECLARE @SearchStr NVARCHAR(200) = 'er,man,s'

-- 执行查询
SELECT h.HeroId, h.HeroName
FROM Hero h
-- 拆分关键词为独立行
CROSS APPLY STRING_SPLIT(@SearchStr, ',') s
-- 不需要区分大小写可在LIKE后加排序规则：COLLATE Chinese_PRC_CI_AS
WHERE h.HeroName LIKE N'%' + s.value + N'%'
GROUP BY h.HeroId, h.HeroName
-- 保证所有关键词都匹配成功
HAVING COUNT(DISTINCT s.value) = (SELECT COUNT(*) FROM STRING_SPLIT(@SearchStr, ','))

方案2：兼容低版本SQL Server（无STRING_SPLIT函数） #

用XML方式实现字符串拆分，无需自定义函数即可运行：

DECLARE @SearchStr NVARCHAR(200) = 'er,man,s'
DECLARE @Xml XML = CONVERT(XML, '<n>' + REPLACE(@SearchStr, ',', '</n><n>') + '</n>')

SELECT h.HeroId, h.HeroName
FROM Hero h
CROSS APPLY (
    SELECT t.n.value('.', 'NVARCHAR(100)') AS keyword
    FROM @Xml.nodes('/n') t(n)
) s
WHERE h.HeroName LIKE N'%' + s.keyword + N'%'
GROUP BY h.HeroId, h.HeroName
HAVING COUNT(DISTINCT s.keyword) = (SELECT COUNT(*) FROM @Xml.nodes('/n') t(n))

效果验证 #

针对题目给出的样例数据，两种方案执行后都会返回HeroName为Superman、Spiderman的两行结果，符合预期。

注意事项 #

• 若关键词包含SQL通配符%、_、[]等，需要提前做转义处理，避免匹配异常
• 可根据业务需求调整LIKE条件的排序规则，实现区分/不区分大小写的匹配

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