Remove nulls from an array in SQL

6
votes

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!

1
sqlarrayshivehiveql

1 Answers

2
votes

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)