Hive--函数--常用内置函数--字符串函数

字符串函数

• 字符串长度函数：length
• 字符串反转函数：reverse
• 字符串连接函数：concat
• 带分隔符字符串连接函数：concat_ws
• 字符串截取函数：substr,substring
• 字符串截取函数：substr,substring
• 字符串转大写函数：upper,ucase
• 字符串转小写函数：lower,lcase
• 去空格函数：trim
• 左边去空格函数：ltrim
• 右边去空格函数：rtrim
• 正则表达式替换函数：regexp_replace
• 正则表达式解析函数：regexp_extract
• URL解析函数：parse_url [HOST,PATH,QUERY,REF,PROTOCOL,FILE,AUTHORITY,USERINFO]
• json解析函数：get_json_object
• 空格字符串函数：space
• 重复字符串函数：repeat
• 首字符ascii函数：ascii
• 左补足函数：lpad
• 右补足函数：rpad
• 分割字符串函数: split
• 集合查找函数: find_in_set
• 聚合函数 collect_list/collect_set
• 数组是否包含函数:array_contains
• 查找函数: instr（str，substr）

字符串长度函数： length

• 语法: length(string A)
• 返回值: int
• 说明：返回字符串A的长度
• 举例：

hive> select length('abcedfg') from dual;
7

字符串反转函数： reverse

• 语法: reverse(string A)
• 返回值: string
• 说明：返回字符串A的反转结果999999举例：

hive> select reverse(abcedfg’) from dual;
gfdecba

字符串连接函数：concat

• 语法：concat (str1,str2,…) ；
• 返回值：string；
• 如有任何一个参数为null ，则返回值为 null。

select concat("my","name");
-- myname


select concat("my",null,"name");
-- null 

带分隔符字符串连接函数：concat_ws

• 语法：concat_ws(string SEP, string1, string2… )；
• 返回值：string；
• 分隔符可以是一个字符串，也可以是其它参数。如果分隔符为 null，则结果为 null，拼接的字符串为null，则会忽略该字符串。

select concat_ws("\t","my","name");
-- my name


select concat_ws(",","my",null,"name");
-- my,name

聚合函数 collect_list/collect_set

• collect_set可以将一列数据中的所有不同值收集到一个集合中,并返回
• collect_set(xxxx)
  • 把同一分组的不同行的数据聚合成一个集合
  • 数据类型 array<>，例如 array<string>
  • select 查看的时候会有左右中括号[]
  • 对null会过滤
• collect_set(xxxx)[0]
  • 用下标可以取某一个
• 经常和concat_ws连用 concat_ws(‘|’,collect_set(xxxx))
• collect_list: 与collect_set的区别就是列的值不去重

select collect_list(province)
    ,collect_set(province)
from (select '北京' as province,'北京' as city
	union all
	select '河南' as province,'郑州' as city
	union all
	select '河南' as province,'郑州' as city
	union all
	select null as province,null as city
)a
;
collect_list(province)	collect_set(province)	
["河南","北京","河南"]	["河南","北京"]	





select province
    ,collect_list(city)
    ,collect_set(city)
    ,concat_ws('|',collect_set(city))
    ,collect_set(concat_ws('|',province,city))
    ,cast(collect_set(concat_ws('|',province,city))  as string) 
from (select '北京' as province,'北京' as city
    union all
    select '北京' as province,'北京' as city
    union all
    select '河南' as province,'郑州' as city
    union all
    select '河南' as province,'洛阳' as city
    union all
    select '河南' as province,'开封' as city
)t
group by province
;


province    collect_list(city)          collect_set(city)            concat_ws('|', collect_set(city))    collect_set(concat_ws('|', province,city))   cast(collect_set(concat_ws('|',province,city)) as string) 
北京        ["北京","北京"]               ["北京"]                      北京                                  ["北京|北京"]                                 [北京|北京]
河南        ["洛阳","郑州","开封"]         ["开封","洛阳","郑州"]         开封|洛阳|郑州                         ["河南|开封","河南|洛阳","河南|郑州"]             [河南|开封, 河南|郑州, 河南|洛阳]

字符串截取函数：substr,substring(string A, intstart, int len)

select substring("myname",0,2);
my

字符串转大写函数：upper,ucase

select upper("myname");
MYNAME

字符串转小写函数：lower,lcase

select lower("MYNAME");
myname

去空格函数：trim

select trim("  myname   ");
myname

左边去空格函数：ltrim

select ltrim("  my  name");
my  name

右边去空格函数：rtrim

select rtrim("  my  name    ");
   my  name

正则表达式替换函数：regexp_replace

• hive–去除特殊字符regexp_replace

SELECT regexp_replace('100-200', '(\\d+)', 'num');
num-num

正则表达式解析函数：regexp_extract

• regexp_extract(str, regexp[, idx])
  • str：被解析的字符串或字段名
  • regexp：正则表达式
  • idx：是返回结果索引，取表达式的哪一()部分<子表达式>，默认值为1
    • 0表示把整个正则表达式对应的结果全部返回；
    • 1表示返回正则表达式中第一个() 对应的结果， 以此类推
• 注意：idx的数字不能大于表达式中()的个数，否则报错
• 注意：如果没有，返回的是空字符串，而不是null

SELECT regexp_extract('100-200', '(\\d+)-(\\d+)', 1)
;--100

select regexp_extract('foothebar', 'foo(.*?)(bar)', 0)
; -- foothebar
select regexp_extract('foothebar', 'foo(.*?)(bar)', 1)
; -- the
select regexp_extract('foothebar', 'foo(.*?)(bar)', 2)
; -- bar


select regexp_extract('isStartDate=2019-07-14', '.*?StartDate\\=([^&]+)',1)
; --2019-07-14

URL解析函数：parse_url [HOST,PATH,QUERY,REF,PROTOCOL,FILE,AUTHORITY,USERINFO]

SELECT parse_url('http://facebook.com/path/p1.php?query=1', 'HOST');
facebook.com

json解析函数：get_json_object

空格字符串函数：space

SELECT space(2);

两个空格

重复字符串函数：repeat

SELECT repeat('123', 2);
123123

首字符ascii函数：ascii

SELECT ascii('31');
51返回3的asc码

左补足函数：lpad

SELECT lpad('hive2', 10, '1');
11111hive2

右补足函数：rpad

SELECT rpad('hive2', 10, '1');
ive211111

分割字符串函数: split

SELECT split('oneAtwoBthreeC', '[ABC]');
["one","two","three",""]

集合查找函数: find_in_set

SELECT find_in_set('ab','abc,b,ab,c,def');
3

数组是否包含函数:array_contains

• array_contains(数组，值)，返回布尔类型值。
• Hive中的array_contains函数与SQL中的 in关键字 操作类似，用于判定
  包含（array_contains）或不包含（!array_contains）关系。与
  in不同的是array_contains可以用于判断一张表中同一个id的多条记录中的同一字段是否包含指定的一个或多个值。
• 需要注意字段类型保持一致，若不一致则需要进行强制类型转换。

select split('[1,2,3,4]', '[\\[\\],]'); 	
-- ["","1","2","3","4",""]


select array_contains(split('[1,2,3,4]', '[\\[\\],]'),'1'); 

-- true

array_contains实例1：替换同一字段多个<>

• where 条件对于同一字段多个<>时可以替换

select * 
from login 
where dt='20220101' 
and (ver !='3.1' and ver !='3.2' 
                 and ver != '4.0' 
                 and ver != '5.2'); 

可以替换为
select * 
from login 
where dt='20220101' 
and !array_contains(split('3.1,3.2,4.0,5.2',','),ver)
;

array_contains实例2：打标签

select
    stu_id
    , case
        when array_contains(collect_set(subject), '语文')
            or array_contains(collect_set(subject), '数学')
            or array_contains(collect_set(subject), '英语')
        then '基础'
        when array_contains(collect_set(subject), '科学') 
        then '拓展' 
        else '其他'
    end as subject_tag_name
from student
where
    dt = '20220101' 
group by
    stu_id

查找函数: instr（str，substr）

• 功能
  • 返回str中第一次出现substr的索引
  • instr函数返回字符串str中子字符串substr第一次出现的位置，其中第一字符的位置是1,如果 str不含substr返回0
• 示例

select instr("abcde",'b');
-- 2

select instr("abcdeabcde",'b');
-- 2

select instr("abcde",'f');
-- 0

select 
case when (instr("你是who呢", '你是') != 0 AND instr("你是who呢", '你是谁') = 0) 
    then '包含：你是而不包含你是谁'
end 
;

--网上有说可以 指定  搜索的开始位置,默认为1，测试不可以
select instr("abcdeabcde",'b',2,1);
--Error while compiling statement: FAILED: SemanticException [Error 10015]: Line 2:7 Arguments length mismatch '1': The function INSTR accepts exactly 2 arguments.

str_to_map

• STR_TO_MAP(VARCHAR text, VARCHAR listDelimiter, VARCHAR keyValueDelimiter)
• 用途：将字符类型数据，转化成map格式的数据，常在转json中使用
• Hive–复杂数据类型array&map&struct
• 使用两个分隔符将文本拆分为键值对。
• 分隔符1将文本分成K-V对，分隔符2分割每个K-V对。
  • 对于分隔符1默认分隔符是 ‘,’，对于分隔符2默认分隔符是 ‘=’。
• 注意：如果key一样，会取最后一个K-V对

select str_to_map("aaaa_-100#bbbb_领券29减8#cccc_29分钟",'#','_') as map_test
-- {"aaa":"领券29减8","bbb":"-100","ccc":"29分钟"}

-- 如果key一样，会取最后一个K-V对
select str_to_map("aaaa_-100#bbbb_领券29减8#cccc_29分钟#cccc_50分钟",'#','_') as map_test
-- {"aaa":"领券29减8","bbb":"-100","ccc":"50分钟"}

rlike、regexp等判断字符串是否包含xxx

• Hive–函数–常用内置函数–关系运算

-- 判断字符串中是否有字母
select *
from( 
    select 'wo' as column_1
    union all
    select 'Wo' as column_1
    union all 
    select '我' as column_1
    )a
where column_1 rlike '[a-zA-Z]'
;

-- 序号	column_1	
-- 1	wo	
-- 2	Wo	

https://blog.csdn.net/weixin_43597208/article/details/135129633