Replace empty string in hive- Nvl and COALESCE tried

user_1616785 0
Viewed 10690

How to replace an empty string(length 0 ) with some other value? Already used Nvl and COALESCE but both doesn't replace with the replacement value because the value is not null. i can use case statement but looking for a built in function if there is any.

hiveql hive null sql
2 Answers

As you are having empty strings so when we use coalesce or nvl works only if we are having null values in the data. These functions won't work with empty strings.

With Empty strings:

hive> select coalesce(string(""),"1");
+------+--+
| _c0  |
+------+--+
|      |
+------+--+
hive> select nvl(string(""),"1");
+------+--+
| _c0  |
+------+--+
|      |
+------+--+

With null values:

hive> select coalesce(string(null),"1");
+------+--+
| _c0  |
+------+--+
| 1    |
+------+--+
hive> select nvl(string(null),"1");
+------+--+
| _c0  |
+------+--+
| 1    |
+------+--+

Try to alter the table and add this property 

TBLPROPERTIES('serialization.null.format'='')

if this property doesn't display empty string as null's then we need to use either case/if statement to replace empty strings.

You can use if statement

if(boolean testCondition, T valueTrue, T valueFalseOrNull)

 hive> select if(length(trim(<col_name>))=0,'<replacement_val>',<col_name>) from <db>.<tb>;

Example:

 hive> select if(length(trim(string("")))=0,'1',string("col_name"));
    +------+--+
    | _c0  |
    +------+--+
    | 1    |
    +------+--+
hive> select if(length(trim(string("1")))=0,'1',string("col_name"));
    +-----------+--+
    |    _c0    |
    +-----------+--+
    | col_name  |
    +-----------+--+
user_76326950

In Hive, empty string is treated like usual comparable value, not NULL. That is why there is no built-in function for this.

Using case statement:

case when col='' or col is null then 'something' else col end
user_27003440
Related