How to check if any field of array not contains substring in Postgres?

Question

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

What I expected is like this:

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

If I use unnest, I will get unpacked array joined with every record(Not expected) like this：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

If I use array_to_string(array, delimiter) with not like, I could get what I wanted as following

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

However, there is a limit: *substr* cann't contains 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

Is there any better way to filter the whole row if any field of comments not contains specified substring?

See dba.stackexchange.com/questions/228235/…

jjanes
– jjanes

2022-03-28 14:02:17 +00:00
Commented Mar 28, 2022 at 14:02 — jjanes
– jjanes, Commented Mar 28, 2022 at 14:02

Philippe · Accepted Answer · 2022-03-28 13:16:49Z

1

If you have a unique id in your table, you can do it like this (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

answered Mar 28, 2022 at 13:16

Philippe

1,8376 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Philippe Over a year ago

there are no integrated functions to do what you need. What's wrong with my solution ?

Philippe Over a year ago

if at least one field of the array contains %substr% it will return the row otherwise it won't. Isn't that the way you want it to run ?

ahuigo Over a year ago

It could not filter the whole row with unnest for this example:` select * from (select id,unnest(comments) as cmts from t1) tmp where cmts not like '%substr%' `

D-Shih · Accepted Answer · 2022-03-28 14:04:22Z

1

You can try to use unnest function.

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

If you don't want to unpack arrays, another way you can try to use ARRAY_TO_STRING function with LIKE.

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

edited Mar 28, 2022 at 14:04

answered Mar 28, 2022 at 13:21

D-Shih

46.5k6 gold badges37 silver badges56 bronze badges

2 Comments

D-Shih Over a year ago

Why don't you want to unpack arrays? Anyway I provide another way for you that you can try

ahuigo Over a year ago

I've fixed my question with more cases. ARRAY_TO_STRING is a good solution, but has a limit(substr cann't contains delimiter)

Collectives™ on Stack Overflow

How to check if any field of array not contains substring in Postgres?

2 Answers 2

3 Comments

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Linked

Related