Hive concat concat_ws 拼接空字符串的坑

﹏ヽ暗。殇╰゛Y 2021-09-24 15:02 578阅读 0赞

**concat() ：如果存在空，返回空：**

hive> select concat('cd','1');
    OK
    cd1
    Time taken: 0.461 seconds, Fetched: 1 row(s)
    hive> select concat('cd',1);
    OK
    cd1
    Time taken: 0.066 seconds, Fetched: 1 row(s)
    hive> select concat('cd',null);
    OK
    NULL
    Time taken: 0.053 seconds, Fetched: 1 row(s)
    hive> select concat('null','cd');
    OK
    nullcd
    Time taken: 0.06 seconds, Fetched: 1 row(s)
    hive> select concat(null,'cd');
    OK
    NULL
    Time taken: 0.056 seconds, Fetched: 1 row(s)
    hive>

hive> select concat(null,'cd','cd');
    OK
    NULL
    Time taken: 0.052 seconds, Fetched: 1 row(s)
    hive> select concat('12','cd','cd');
    OK
    12cdcd
    Time taken: 0.051 seconds, Fetched: 1 row(s)
    hive> select concat('12',null,'cd');
    OK
    NULL
    Time taken: 0.064 seconds, Fetched: 1 row(s)
    hive> select concat('12','cd',null);
    OK
    NULL
    Time taken: 0.047 seconds, Fetched: 1 row(s)
    hive>

**concat\_ws:如果存在空，返回非空的拼接：**

hive> select concat_ws('_','a','c','v','d');
    OK
    a_c_v_d
    Time taken: 0.06 seconds, Fetched: 1 row(s)
    hive> select concat_ws('_',null,'c','v','d');
    OK
    c_v_d
    Time taken: 0.047 seconds, Fetched: 1 row(s)
    hive> select concat_ws('_',null,'c',null,'d');
    OK
    c_d
    Time taken: 0.053 seconds, Fetched: 1 row(s)
    hive> select concat_ws(null,'a','c','v','d');
    OK
    NULL
    Time taken: 0.055 seconds, Fetched: 1 row(s)
    hive>
    
    hive> select concat_ws('_',null,'c',null);
    OK
    c
    Time taken: 0.072 seconds, Fetched: 1 row(s)
    hive> select concat_ws('_',null,null,null);
    OK
    
    Time taken: 0.049 seconds, Fetched: 1 row(s)
    hive>

Hive 2.0.0-SNAPSHOT

更多企业内的技术应用和使用技巧，请移步至我的公众号【程序员实用技能】

![图片][95678de3fba567a94f838b93808bff1e.png]

[95678de3fba567a94f838b93808bff1e.png]: /images/20210923/d18e7347e5874caf84f39fa090021a09.png