Snowflake LISTAGG function creating empty strings instead of nulls

Question

I am using the LISTAGG() function of Snowflake and I noticed that the resulting column does not filter correctly on the condition col IS NOT NULL anymore because it interprets all missing values as empty strings, hence col != '' seems to be the way to go for me.

As I think it's not clear, is there a way to explicitly and properly create a column with LISTAGG and assign NULL in case the result is an empty string?

So far what I came up with was just something like this, but it's not efficient as I am outputting two columns:

listagg(distinct col3, ', ') AS col1, IFF(col1 = '', NULL, col1) AS col2

Is there a way to do it in a single row?

Answer 1

Using NULLIF to replace empty string '' with NULL:

SELECT LISTAGG(DISTINCT NULLIF(col3, ''), ', ') AS col1

---

LISTAGG by definition skips NULL values:

Returns a string that includes all of the non-NULL input values, separated by the delimiter.