PostgreSQL如何判断字段是否包含数组中任一子字符串？

问题场景 #

现有如下PostgreSQL数据表：

one | two   | three |    code    | four 
-----+-------+-------+------------+------
   0 | 21750 | 10110 |    1016916 | 20.0
   0 | 21750 | 10111 |    1017949 | 20.2
   0 | 21750 | 10115 |     101792 | 21.0
   0 | 21737 | 10110 |     100753 | 20.0
   0 | 21737 | 10110 | 14343054   | 20.0

需要提取所有code字段包含794、43、17任一子字符串的记录。

更新说明 #

已知可以通过多条件ILIKE实现：

select * from mytable where
code ilike '%794%' or
code ilike '%43%' or
code ilike '%17%';

但实际场景中待匹配的子字符串是以数组形式（例如('794', '43', '17')）给出，且数量超过100，不想拆写成大量OR条件。尝试使用数组重叠运算符&&时出现错误：

提示：未找到匹配给定名称和参数类型的运算符。您可能需要显式转换类型。

解决方案 #

方法1：使用ANY结合ILIKE（拼接通配符） #

直接将数组元素拼接%后，用ANY匹配：

SELECT *
FROM mytable
WHERE code::text ILIKE ANY (ARRAY['%794%', '%43%', '%17%']);

如果原始数组不带通配符，可以用array_to_string和string_to_array快速拼接：

-- 假设待匹配数组为('794', '43', '17')
SELECT *
FROM mytable
WHERE code::text ILIKE ANY (
    string_to_array(
        '%' || array_to_string('{"794", "43", "17"}'::text[], '%|%') || '%',
        '|'
    )
);

方法2：使用unnest展开数组+EXISTS子查询 #

通过unnest将数组展开为行，再检查子字符串是否存在：

SELECT *
FROM mytable
WHERE EXISTS (
    SELECT 1
    FROM unnest('{"794", "43", "17"}'::text[]) AS substr
    WHERE position(substr IN code::text) > 0
);

也可以用strpos函数替代position，效果一致：

SELECT *
FROM mytable
WHERE EXISTS (
    SELECT 1
    FROM unnest('{"794", "43", "17"}'::text[]) AS substr
    WHERE strpos(code::text, substr) > 0
);

错误原因说明 #

之前使用&&运算符报错，是因为&&是数组重叠运算符，要求左右两边都是数组类型，但code字段是数值/字符串类型，与数组类型不匹配，因此需要显式转换类型或改用字符串匹配方法。

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