snowflake-cloud-data-platform

Snowflake - Invalid argument > types for function 'IFF': (VARCHAR(16777216), VARCHAR(16777216), NULL)

I have 3 columns to look through, name, name2 and name3, and only 1 column in each row has a value that looks something like this with a word followed by a list randomword [m=444] I have to check each column then I have to extract the numbers in that list so from my example I would need 444. I've tried the below:

Select *,
CASE
 WHEN REGEXP_SUBSTR(NAME, '(?:\[m=)') THEN REGEXP_SUBSTR(NAME, '[[]m=([0-9]+)'),
 WHEN REGEXP_SUBSTR(NAME2, '(?:\[m=)') THEN REGEXP_SUBSTR(NAME2, '[[]m=([0-9]+)'),
WHEN REGEXP_SUBSTR(NAME3, '(?:\[m=)') THEN REGEXP_SUBSTR(NAME3, '[[]m=([0-9]+)'),
ELSE null
END
from my_table

but now I'm seeing this error:

SQL compilation error: error line 2 at position 0 Invalid argument types for function 'IFF': (VARCHAR(16777216), VARCHAR(16777216), NULL)

Solution

  • Your condition in your CASE expression isn't a condition. Regexp_Substr returns either a string or NULL. It does not return a TRUE or FALSE as is required by the WHEN clause.

    Instead you'll want to test if the return from REGEXP_SUBSTR IS NOT NULL:

     CASE
 WHEN REGEXP_SUBSTR(NAME, '(?:\[m=)') IS NOT NULL THEN REGEXP_SUBSTR(NAME, '[[]m=([0-9]+)')
 WHEN REGEXP_SUBSTR(NAME2, '(?:\[m=)') IS NOT NULL THEN REGEXP_SUBSTR(NAME2, '[[]m=([0-9]+)')
WHEN REGEXP_SUBSTR(NAME3, '(?:\[m=)') IS NOT NULL THEN REGEXP_SUBSTR(NAME3, '[[]m=([0-9]+)')
ELSE null
END