CODEWITHSUNDEEP
sql-likesnowflake-cloud-data-platform
Saqib Ali
Saqib Ali

Reputation: 4400

NOT LIKE ANY query in Snowflake

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.

Upvotes: 26

Views: 42887

Answers (3)

Lukasz Szozda
Lukasz Szozda

Reputation: 175556

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:

enter image description here

Upvotes: 2

H Roy
H Roy

Reputation: 635

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%'))

enter image description here

Upvotes: 1

David Garrison
David Garrison

Reputation: 2880

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.

Upvotes: 36

Related Questions