Query to check each element in a list against a column in an BigQuery table and select a record if none of the item is present from that list
Consider below approach
select *
from (
select column_name,
array(
select word
from unnest(split(column_name, ' ')) word
left join unnest(arr) phrase
group by word
having countif(regexp_contains(phrase, word)) = 0
) items_not_present_in_lists
from (
select *, array (
select phrase from phrases
where regexp_contains(column_name, phrase)
) arr
from your_table
)
)
where array_length(items_not_present_in_lists) > 0
if applied to sample data in your question - output is
I was using below dummy data for testing/playing with (so you can too)
with your_table as (
select 'foo' column_name union all
select 'foo1 bar foobar' union all
select 'foobar foo2 bar1' union all
select 'bar foo1' union all
select 'baz foobar' union all
select 'etc'
), phrases as (
select phrase
from unnest(['foo', 'bar', 'foo1 bar', 'foobar', 'foo2 bar1', 'foo1 foobar', 'foo foo1 foo2', 'etc']) phrase
)