Replace empty string in hive- Nvl and COALESCE tri

Question

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.

Answer 1

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

Answer 2

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  |
    +-----------+--+