Remove nulls from an array in SQL

Question

Want to remove nulls from an array in hive/sql

for example : array is ['1',null] after converting to string values it should be '1' only.

to split the array I am using below:

concat_ws( ",", array_val)

this gives : 1,null

required output : 1

Thanks for the help!

leftjoin · Accepted Answer · 2018-06-25 09:22:09Z

2

Use regexp_replace to remove null from concatenated string:

hive> select regexp_replace('null,1,2,null,2,3,null','(,+null)|(^null,)','');
OK
1,2,2,3
Time taken: 6.006 seconds, Fetched: 1 row(s)

answered Jun 25, 2018 at 9:22

leftjoin

38.5k8 gold badges64 silver badges126 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1