NOT LIKE ANY query in Snowflake

Question

I am trying to run the following query in Snowflake:

SELECT * FROM chapters 
WHERE
title NOT LIKE ANY ('Summary%', 'Appendix%')

but it errors out. I know Snowflake support LIKE ANY query syntax. But I am not sure why my query is not working.

Answer 1

It does seem like that syntax with NOT should work, and I'm not quite sure why it doesn't, but this works.

SELECT * FROM chapters 
WHERE
NOT (title LIKE ANY ('Summary%', 'Appendix%'))

Extra parens are optional, but seems more clear to me when it's "worded" this way.

Answer 2

The query execution looks like this when we see the profile for the below query.

select cc_name from "SNOWFLAKE_SAMPLE_DATA"."TPCDS_SF100TCL"."CALL_CENTER"
where not(cc_name  like any ('North%', 'Califor%'))

Answer 3

Snowflake supports function version of syntax for LIKE ANY.

SHOW FUNCTIONS LIKE 'LIKE_ANY';

name	min_num_arguments	max_num_arguments	arguments	description
LIKE_ANY	3	-1	LIKE_ANY(VARCHAR, VARCHAR, VARCHAR)	RETURN BOOLEAN returns TRUE if subject LIKE any of the patterns

The signature is as follows:

_{* as the moment of writing there is no documentation webpage}

LIKE_ANY(subject, escape_character, pattern1, pattern2, ...)

---

For query in question the equivalent is:

SELECT * 
FROM chapters 
WHERE NOT LIKE_ANY(title, '!', 'Summary%', 'Appendix%');

Input data:

CREATE OR REPLACE TABLE chapters(title TEXT) AS 
SELECT 'Summary' UNION 
SELECT 'Addendum' UNION
SELECT 'Appendix 1';

Output: