Using the SPLIT_TO_TABLE function conditionally

user_7615751 0
Viewed 47

I'm using SPLIT_TO_TABLE to split a column which has multiple comma separated values

The input table looks like this:

enter image description here

I'm using the following query:

SELECT ID,DATE_KEY,COUNTRY, y.value AS new_sub
FROM TABLE, LATERAL split_to_table(TABLE.SUBDIVISIONS, ', ') y

Which results in the following output:

enter image description here

I want to have the rows that have NULL subdivision also included in the output.

Is there a way to conditionally use the function to just apply when the column is a non null value or would it be better to just join two outputs (one where subdivision is null and the result of my query)?

snowflake-cloud-data-platform
2 Answers

You can use STRTOK_TO_ARRAY() to convert SUBDIVISION into an ARRAY and then use a Lateral Flatten with outer => TRUE

with tt as ( Select * 
FROM (VALUES
(98,TO_DATE('1/01/2020','DD/MM/YY'),'DE',NULL),
(99,TO_DATE('6/01/2020','DD/MM/YY'),'DE','DE,DE-BY,DE-ST'),
(100,TO_DATE('8/03/2020','DD/MM/YY'),'DE','DE_BE'),
(101,TO_DATE('20/03/2020','DD/MM/YY'),'DE',NULL)
) AS tt (ID, DATE_KEY, COUNTRY, SUBDIVISIONS ) )

SELECT ID,DATE_KEY,COUNTRY, y.value AS new_sub
FROM TT, LATERAL FLATTEN ( INPUT => STRTOK_TO_ARRAY(TT.SUBDIVISIONS, ','), OUTER => TRUE) y;
user_168507500

Alternatively, you could make split_to_table preserve nulls by assigning your delimiter ',' to subdivisions when they are null using coalesce. If you prefer null instead of blanks, you can use nullif in the outer select

select id, date_key, country, nullif(y.value,'') as new_sub
from t, lateral split_to_table( coalesce(t.subdivisions,',') , ', ') y
user_99471590
Related