– 建表
create table if not exists test.tb_str(
id int,
str string
)
row format delimited fields terminated by ",";
– 导入
vi /doit/tb_str.txt
1,hello
2,world
3,sun
4,door
load data local inpath "/doit/tb_str.txt" into table test.tb_str;
– 解法
with tmp as (
select
id,
str,
case
when str like '%e%' and str like '%o%' then "e,o"
when str like '%e%' then "e"
when str like '%o%' then "o"
end as key
from test.tb_str)
select
id,
str,
keys
from tmp
lateral view
explode(split(key,",")) k as keys