PostgreSQL如何检查冒号分隔字符串列是否包含整数值0或1

核心实现思路 #

将role字段按:分隔符拆分为数组，判断数组中是否存在0或1元素即可完成过滤。

最简实现方案 #

使用PostgreSQL内置的字符串转数组函数+数组重叠运算符，写法最简洁，性能也足够应对多数场景：

SELECT Customer, role
FROM "Table A"
WHERE string_to_array(role, ':') && ARRAY['0', '1'];

• string_to_array(role, ':')：将role字段按冒号拆分为文本数组，例如0:1:2会转换为{'0','1','2'}
• &&为数组重叠运算符，只要两侧数组存在任意共同元素就返回true，此处用来判断拆分结果是否包含0或1

可选实现（需校验整数类型时使用） #

如果需要先将拆分后的内容转为整数再做校验，可以搭配unnest函数拆分行后过滤：

SELECT DISTINCT a.Customer, a.role
FROM "Table A" a
CROSS JOIN UNNEST(string_to_array(a.role, ':')::int[]) AS role_val
WHERE role_val IN (0, 1);

结果验证 #

针对示例样本数据执行上述语句，返回结果如下：

客户B的role值拆分后仅包含19，不满足条件会被正常过滤。

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