1
votes

Assuming a source table with structure:

personid INT, 
name STRING,
mobilephone STRING,
homephone STRING

and target table structure:

personid INT, 
telephone ARRAY<STRING>

To insert into target from source, I did

select 
personid,
array(mobilephone, homephone)
from source_table

12, ["+1958523366","+1258523366"]
13, ["+1958523366",""]

Second row in result shows empty string because homephone is null in source table. How to return something like this instead:

12, ["+1958523366","+1258523366"]
13, ["+1958523366"] 

i.e. return item in array only if not null

1

1 Answers

0
votes
select 
personid,
split(concat_ws(',',mobilephone,homephone),',')
from source_table