CODEWITHSUNDEEP
sqlcasesnowflake-cloud-data-platform
PiCubed
PiCubed

Reputation: 395

sql contains() with multiple conditions

I am using snowflakes db and I want to write contain with multiple conditions like below:

SELECT 
    CASE 
        WHEN CONTAINS(temp_CREATIVE_SIZE, '"480x320" OR "320x48"') THEN '320x480'
        WHEN CONTAINS(temp_CREATIVE_SIZE, '1024x768')  THEN '768x1024'
        WHEN CREATIVE_NAME ILIKE '%test%' THEN NULL
        WHEN CONTAINS(CREATIVE_NAME, '"interstitialvideo" OR "videointerstitial"') THEN '320x480'
        ELSE temp_CREATIVE_SIZE 
        END AS CREATIVE_SIZE
FROM my_schema

However the output still gives me 480x320 and 320x48. The contains() with single case works fine, it's the multiple cases that is not working. Is there another way of writing contains that I am not aware of?

Upvotes: 1

Views: 10530

Answers (2)

Felipe Hoffa
Felipe Hoffa

Reputation: 59165

An alternative that supports multiple patterns is LIKE ANY.

Instead of:

WHEN CONTAINS(SIZE, '"480x320" OR "320x48"') THEN '320x480'
// doesn't work

You can write:

WHEN SIZE LIKE ANY ('%480x320%', '%320x48%') THEN '320x480'
// works

https://docs.snowflake.com/en/sql-reference/functions/like_any.html

Upvotes: 3

Simeon Pilgrim
Simeon Pilgrim

Reputation: 25903

CONTAINS is not a regex, it's faster than ILIKE because it's just a substring present or not.

Thus reworking you demo problem

SELECT size,
    CASE 
        WHEN CONTAINS(SIZE, '"480x320" OR "320x48"') THEN '320x480'
        WHEN CONTAINS(SIZE, '1024x768')  THEN '768x1024'
        WHEN size ILIKE '%test%' THEN NULL
        WHEN CONTAINS(size, '"interstitialvideo" OR "videointerstitial"') THEN '320x480'
        ELSE 'else_'||size 
        END AS CREATIVE_SIZE
FROM (VALUES ('a480x320'),('480x320a'),('a320x480'),('320x480a'),('3testa'),('1024x768a') v(size));

gives:

SIZE    CREATIVE_SIZE
a480x320    else_a480x320
480x320a    else_480x320a
a320x480    else_a320x480
320x480a    else_320x480a
3testa      null
1024x768a   768x1024

showing you OR does not work.

What you want it to OR the CONTIANS or just have two WHERE CLAUSES

SELECT size,
    CASE 
        WHEN CONTAINS(SIZE, '480x320') THEN '320x480'
        WHEN CONTAINS(SIZE, '320x48') THEN '320x480'
        WHEN CONTAINS(SIZE, '1024x768')  THEN '768x1024'
        WHEN size ILIKE '%test%' THEN NULL
        WHEN CONTAINS(size, '"interstitialvideo" OR "videointerstitial"') THEN '320x480'
        ELSE 'else_'||size 
        END AS CREATIVE_SIZE
FROM (VALUES ('a480x320'),('480x320a'),('a320x480'),('320x480a'),('3testa'),('1024x768a') v(size));

gives:

SIZE    CREATIVE_SIZE
a480x320    320x480
480x320a    320x480
a320x480    320x480
320x480a    320x480
3testa  
1024x768a   768x1024

or:

WHEN CONTAINS(SIZE, '480x320') OR CONTAINS(SIZE, '320x48') THEN '320x480'

Upvotes: 0

Related Questions