替换配置单元中的空字符串-Nvl和COALESCE已尝试 [英] Replace empty string in hive- Nvl and COALESCE tried

问题描述

如何用其他值替换空字符串(长度为0)?已经使用了 Nvl 和 COALESCE ，但是两者都不替换为替换值，因为该值不为null.我可以使用 case 语句，但是如果有内置函数，则在寻找它.

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.

推荐答案

由于您有空字符串，因此当我们使用 coalesce或nvl 时，仅当我们具有 null值".这些功能不适用于空字符串.

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.

带空字符串:

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

具有空值:

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'='')

如果此属性未将空字符串显示为null，则我们需要使用 case/if 语句替换空字符串.

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

您可以使用 if语句

You can use if statement

if(布尔值testCondition，T值True，T值FalseOrNull)

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

示例:

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

这篇关于替换配置单元中的空字符串-Nvl和COALESCE已尝试的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！