Sha2 function return empty/null values in snowflake

Question

I am using sh2 function to generate the hash value but It is generating null/empty if any of the column value has null/empty.

For example

Input

select sha2(NULL, 256);

Output

NULL

is there any way to generate value even when the value inside sha2 function is empty/null.

Answer 1

NULL in SQL, means no value, so the result you are getting is expected.

You can create a query that will check for NULLs and return some predefined or random output for them, depending on what value you want, and the hash when there is the actual value. Example:

Create some test data:

create or replace table test_table (col1 string);

insert into test_table values ('string1'), (null), ('string2');

Return hash when it is not NULL

select
    col1,
    case 
        when col1 is null then 'value you want get instead of the hash'
        else sha2(col1, 256)
    end as result
from test_table;

Output:

Alternatively, you can output some random hash instead of a predefined string:

select
    col1,
    case 
        when col1 is null then sha2(random())
        else sha2(col1, 256)
    end as result
from test_table;

Output: