Try to understand the BigQuery behaviour of the ANY operator (see also here
The following statement returns as expected true:
SELECT 'a' LIKE ANY ('b','%b%', 'b', 'a');
--true
It basically answers the question, is there any 'a' in the pattern, yes it is!
Now inverting the statement also returns true:
SELECT 'a' NOT LIKE ANY ('b','%b%', 'b', 'a');
-- true
In this case the question would be is there not any 'a' in the pattern, which is false, there is a 'a' in the pattern.
Can somebody help me to understand the behaviour of BigQuery? thanks!
Was reading throw the documentation which states: ANY: Checks if the set of patterns contains at least one pattern that matches the search value
No, the second query asks the database if in the tupel are elements that are not a
And as there are 3 You get true.
For what you are searching is NOT IN