HIVE-字符串相关函数的使用

痛定思痛。 2022-09-16 11:03 240阅读 0赞

### 字符串相关函数 ###

*  UPPER:转大写
 *  LOWER:转小写
 *  Length:长度
 *  TRIM:前后去除空格
 *  lpad,rpad:左填充/右填充
 *  regexp\_replace:使用正则表达式匹配目标字符串,匹配成功后替换
 *  SIZE:返回集合中的元素个数
 *  MAP\_KEYS:返回map当中的key
 *  MAP\_VALUES:返回map当中的VALUES
 *  ARRAY\_CONTAINS:判断array中是否包含某个元素
 *  SORT\_ARRAY:按照字段顺序排序

# UPPER:转大写 #

hive (default)> select upper('low');
    OK
    _c0
    LOW
    Time taken: 0.258 seconds, Fetched: 1 row(s)

# LOWER:转小写 #

hive (default)> select lower('LOW');
    OK
    _c0
    low
    Time taken: 0.127 seconds, Fetched: 1 row(s)

# Length:长度 #

hive (default)> select length('Facebook');
    OK
    _c0
    8
    Time taken: 0.167 seconds, Fetched: 1 row(s)

# TRIM:前后去除空格 #

hive (default)> select trim(' tst ww ');
    OK
    _c0
    tst  ww
    Time taken: 0.102 seconds, Fetched: 1 row(s)

# lpad,rpad:左填充/右填充 #

截取字符串,当长度超过字符串的时候,进行拼接。一个是从左边进行拼接,一个是从右边进行拼接

hive (default)> select lpad('facebook',2,'ww');
    OK
    _c0
    fa
    Time taken: 0.142 seconds, Fetched: 1 row(s)
    
    hive (default)> select rpad('facebook',12,'ww');
    OK
    _c0
    facebookwwww
    Time taken: 0.116 seconds, Fetched: 1 row(s)

# regexp\_replace:使用正则表达式匹配目标字符串,匹配成功后替换 #

hive (default)> select regexp_replace('2020/10/25','/','-');
    OK
    _c0
    2020-10-25
    Time taken: 0.09 seconds, Fetched: 1 row(s)
    //例如date_add函数中不识别分隔符为/,只识别-,那么可以用这个函数进行替换

# SIZE:返回集合中的元素个数 #

hive (default)> select * from test3;
    OK
    test3.name	test3.friends	test3.children	test3.address
    songsong	["bingbing","lili"]	{ "xiao song":18,"xiaoxiao song":19} { "street":"hui long guan","city":"beijing"}
    yangyang	["caicai","susu"]	{ "xiao yang":18,"xiaoxiao yang":19} { "street":"chao yang","city":"beijing"}
    Time taken: 0.1 seconds, Fetched: 2 row(s)
    hive (default)> select size(friends) from test3;
    OK
    _c0
    2
    2
    Time taken: 0.124 seconds, Fetched: 2 row(s)

# MAP\_KEYS:返回map当中的key #

hive (default)> SELECT MAP_KEYS(children) from test3;
    OK
    _c0
    ["xiao song","xiaoxiao song"]
    ["xiao yang","xiaoxiao yang"]
    Time taken: 0.106 seconds, Fetched: 2 row(s)

# MAP\_VALUES:返回map当中的VALUES #

hive (default)> SELECT MAP_VALUES(children) from test3;
    OK
    _c0
    [18,19]
    [18,19]
    Time taken: 0.19 seconds, Fetched: 2 row(s)

# ARRAY\_CONTAINS:判断array中是否包含某个元素 #

第一个数组中有,第二个数组中没有,所以一个true,一个false

hive (default)> select array_contains(friends,'bingbing') from test3;
    OK
    _c0
    true
    false
    Time taken: 0.074 seconds, Fetched: 2 row(s)

# SORT\_ARRAY:按照字段顺序排序 #

hive (default)> select sort_array(friends) from test3;
    OK
    _c0
    ["bingbing","lili"]
    ["caicai","susu"]
    Time taken: 0.118 seconds, Fetched: 2 row(s)
    
    hive (default)> select * from test3;
    OK
    test3.name	test3.friends	test3.children	test3.address
    songsong	["bingbing","lili"]	{ "xiao song":18,"xiaoxiao song":19} { "street":"hui long guan","city":"beijing"}
    yangyang	["caicai","susu"]	{ "xiao yang":18,"xiaoxiao yang":19} { "street":"chao yang","city":"beijing"}
    Time taken: 0.08 seconds, Fetched: 2 row(s)