postgresql 如何检查数组中是否有字段不包含子字符串?

amrnrhlw  于 11个月前  发布在  PostgreSQL
关注(0)|答案(2)|浏览(205)

如何检查数组中是否有字段不包含子字符串?

$ select * from blogs;
 id  |   comments
-------+---------------
 1 | {str1,str2,str3}
 2 | {_substr_,str2,str3}

字符串
我所期待的是这样的:

> select * from mytable where ANY(comments)  not like '%substr%';
     id  |   comments
    -------+---------------
     1 | {str1,str2,str3}


如果我使用unnest,我将得到解压缩的数组与每个记录(不期望)连接,如下所示:https://www.db-fiddle.com/f/9997TuKMMzFUUuyr5VJX7a/0

> select * from (select id,unnest(comments) as cmts from t1) tmp where cmts not like '%substr%'
  id  | cmts
-------+------
 1 | str1
 1 | str2
 1 | str3
 2 | str2
 2 | str3


如果我使用array_to_string(array, delimiter)not like,我可以得到我想要的,如下所示

> select * from (select id,array_to_string(comments, ',') as cmts from blogs) tmp where cmts not like '%substr%';
 id  |      cmts
-------+----------------
 1 | str1,str2,str3


但是,有一个限制:*substr*不能包含delimiter

# select * from (select id,array_to_string(comments, ',') as cmts from blogs) tmp where cmts not like '%str1,str2%';
 id  |        cmts
-------+--------------------
 2 | _substr_,str2,str3


如果comments的任何字段不包含指定的子字符串,是否有更好的方法来过滤整行?

0mkxixxg

0mkxixxg1#

如果你的表中有一个唯一的id,你可以这样做(result here

with x as (select *,unnest(arrays) as to_text from t1)
select t1.*
from t1,x 
where x.to_text ilike '%sutstr%'
and x.id = t1.id

字符串

50few1ms

50few1ms2#

你可以尝试使用unnest函数。

select *
from (
  select *,unnest(arrays) as val 
  from mytable
) tt 
WHERE pub_types like '%sutstr%'

字符串
如果你不想解压缩数组,另一种方法是尝试将ARRAY_TO_STRING函数与LIKE一起使用。

SELECT *
FROM mytable
where ARRAY_TO_STRING(pub_types, ',') LIKE '%sutstr%'

相关问题