字符串函数
- 字符串函数
- 字符串长度函数: length
- 字符串反转函数: reverse
- 字符串连接函数:concat
- 带分隔符字符串连接函数:concat_ws
- 聚合函数 collect_list/collect_set
- 字符串截取函数:substr,substring(string A, intstart, int len)
- 字符串转大写函数: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
- 数组是否包含函数:array_contains
- 查找函数: instr(str,substr)
- str_to_map
- rlike、regexp等判断字符串是否包含xxx
字符串函数
- 字符串长度函数: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
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
-- 判断字符串中是否有字母
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