Menu
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!
859
There are two ways to replace NULL with blank values in SQL Server, function ISNULL(), and COALESCE(). Both functions replace the value you provide when the argument is NULL like ISNULL(column, '') will return empty String if the column value is NULL.
PostgreSQL ARRAY_AGG() function is an aggregate function that accepts a set of values and returns an array where each value in the input set is assigned to an element of the array. Syntax: ARRAY_AGG(expression [ORDER BY [sort_expression {ASC | DESC}], [...])
Answer: A. Except COUNT function, all the group functions ignore NULL values.
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
