需求
SELECT A.*
FROM ZONE A;
得:2000,8000;70271,19147;900001,0;
求:70271->19147 /900001->0
解决
思路1:键值对
-- 方法一:函数比较不通用
select str_to_map(element_str,'\073',',')['70451']
from ZONE;
思路2:
①split(hivesql 可行,mysql不可行)
set hivevar:var='|200:330|233:235|23:24|';
select if(instr(${hivevar:var},'|230:')>0,
split(substr(substr(${hivevar:var},instr(${hivevar:var},'|230:')),length('|230:')+1),'\|')[0],'');
②较为通用,但语句比较臃肿
set hivevar:var='|200:330|233:235|23:24|';
select if(instr(${hivevar:var},'|230:')>0,
substr(substr(substr(${hivevar:var},instr(${hivevar:var},'|230:')),length('|230:')+1),
1,
instr(substr(substr(${hivevar:var},instr(${hivevar:var},'|230:')),length('|230:')+1),'|')-1
)
,'');
思路3:正则表达式
select regexp_extract(REGEXP_extract(concat('\073',a.element_str),'(\073900007,[^\073]*)',1), '(?<=,)(.*)',1),
a.element_str
from zone a
注意事项
在目标字段前加; 防止出现匹配结果一致的情况
;13,23;1913,23
如果前面不加;查找13时容易出现多个匹配项