Create hash by ignoring null in snowflake

Question

I want to create hash of ('a', 'b', 'c', null) by ignoring null. I used below statement to do the same but it returns null.

I want (select SHA2_HEX(a|b|c) whereas Below statement does (select SHA2_HEX(null)

(select SHA2_HEX(CONCAT_WS('|', 'a', 'b', 'c', null)))

Answer 1

CONCAT_WS will produce NULL as soon as one of the values is NULL. Try to add coalesce(your_column, ''), then at least the final output of CONCAT_WS is not NULL. But: Result is still not correct, because you will have |a|b|c| (note the last |).

select SHA2_HEX(CONCAT_WS('|', 'a', 'b', 'c', coalesce(null, '')))

Otherwise just do CONCAT('|', 'a', '|', 'b', '|', 'c', coalesce(null, ''))

Answer 2

ARRAY_CONSTRUCT_COMPACT drops NULL's and then ARRAY_TO_STRING gives the string you are looking for:

select 
    CONCAT_WS('|', 'a', 'b', 'c', null) as d1
    ,array_construct('a', 'b', 'c', null) as a1
    ,ARRAY_TO_STRING(a1, '|') as d2
    ,array_construct_compact('a', 'b', 'c', null) as a2
    ,ARRAY_TO_STRING(a2, '|') as d3
    ;

D1	A1	D2	A2	D3
null	[ "a", "b", "c", undefined ]	a|b|c|	[ "a", "b", "c" ]	a|b|c

thus

select SHA2_HEX(ARRAY_TO_STRING(array_construct_compact('a', 'b', 'c', null),'|'));

gives:

SHA2_HEX(ARRAY_TO_STRING(ARRAY_CONSTRUCT_COMPACT('A', 'B', 'C', NULL),'|'))
a52dd81bfd5e4e66d96b9f598382f6cbf8c5c3897654e6ae9055e03620fcf38e