CODEWITHSUNDEEP
snowflake-cloud-data-platform
Shruti
Shruti

Reputation: 135

BLANK handling in Snowflake

What should be the code for handling blanks in snowflake?

for NULL, we do like

nvl (col1 , 'NA')

However, for blank or empty space etc what is the code in snowflake?

Upvotes: 0

Views: 5705

Answers (2)

Lukasz Szozda
Lukasz Szozda

Reputation: 176094

For handling '', ' ' or NULL the following code could be used:

SELECT col, COALESCE(NULLIF(TRIM(col), ''), 'NA')
FROM VALUES ('text'), (NULL), (''), (' '), ('   ') AS s(col);

Output:

enter image description here

Rationale:

  • TRIM - removing spaces
  • NULLIF - nullifying empty string
  • COAELSCE - handling NULL

Upvotes: 1

Sergiu
Sergiu

Reputation: 4608

You can do it with a CASE or IFF, for example:

select
    column1,
    case
        when column1='' then 'blank'
        when column1=' ' then 'space'
        else 'NA'
    end as result
from (values(''),(' '),('x')) v;

I get back:

enter image description here

With IFF you can try something like:

set a = ' ';
select iff($a = ' ', 'space', 'NA');
set b = '';
select iff($b = '', 'blank', 'NA');

Upvotes: 0

Related Questions