SQL Server 2017中如何判断数组任意元素是否为表列值的子串

针对逗号分隔存储多设备名的字段匹配需求，不同场景可以用以下方式实现：

通用兼容写法（适配所有关系型数据库） #

数组元素较少时可以直接拼接多个LIKE条件，示例如下：

SELECT * FROM 你的表名
WHERE 
  equipments LIKE '%PKM_119160.000%'
  OR equipments LIKE '%PKM_119160.216%';

注意：该写法不依赖数据库专属语法，兼容性最高，但数组元素过多时语句会比较冗长，且前后加%的模糊匹配无法走普通索引，数据量大时查询性能较差。如果存在短设备名刚好是长设备名子串的场景，还可能出现误匹配，建议仅作为临时方案使用。

主流数据库专属优化写法 #

MySQL / MariaDB #

可以用正则匹配简化多条件拼接，注意转义特殊字符避免误匹配：

SELECT * FROM 你的表名
-- 用|分隔多个匹配值，点号需要转义避免匹配任意字符
WHERE equipments REGEXP 'PKM_119160\\.000|PKM_119160\\.216';

8.0以上版本支持JSON_TABLE的场景也可以直接传入JSON数组匹配，适合数组元素动态变化的场景：

SELECT * FROM 你的表名
WHERE EXISTS (
  SELECT 1 FROM JSON_TABLE(
    '["PKM_119160.000", "PKM_119160.216"]',
    '$[*]' COLUMNS(device VARCHAR(100) PATH '$')
  ) AS tmp
  -- 先去掉字段里的空格再匹配
  WHERE FIND_IN_SET(tmp.device, REPLACE(equipments, ' ', '')) > 0
);

PostgreSQL #

可以直接用ANY运算符结合数组参数，写法最简洁：

SELECT * FROM 你的表名
WHERE equipments LIKE ANY (
  ARRAY['%PKM_119160.000%', '%PKM_119160.216%']
);

要避免子串误匹配可以拆分字段后精确匹配：

SELECT DISTINCT t.* 
FROM 你的表名 t, unnest(string_to_array(equipments, ', ')) e
WHERE e = ANY (ARRAY['PKM_119160.000', 'PKM_119160.216']);

SQL Server #

用STRING_SPLIT拆分字段后匹配：

SELECT DISTINCT t.* FROM 你的表名 t
CROSS APPLY STRING_SPLIT(REPLACE(t.equipments, ' ', ''), ',') e
WHERE e.value IN ('PKM_119160.000', 'PKM_119160.216');

长期优化建议 #

如果该查询是业务高频查询，强烈建议调整表结构，废除逗号拼接存储多值的设计，新增独立的设备关联表，单条记录存储一个设备对应关系，调整后可以直接用IN运算符查询，不仅可以走索引大幅提升性能，也能完全避免子串误匹配的问题。

内容的提问来源于stack exchange，提问作者Md. Shohag Mia