--利用instr函数查找分隔符第一次出现的位置
instr(p.str_text, ':',1,1)
原表数据:
--第二次拆分按冒号':'分隔的字符串 select p.str_text, TRIM(substr(p.str_text, 0, instr(p.str_text, ':')-1)) as KM1, substr(p.str_text, instr(p.str_text, ':',1,1)+1,instr(p.str_text, ':',1,2)-instr(p.str_text, ':',1,1)-1) AS KM2, substr(p.str_text, instr(p.str_text, ':',1,2)+1,instr(p.str_text, ':',1,3)-instr(p.str_text, ':',1,2)-1) AS KM3, substr(p.str_text, instr(p.str_text, ':',1,3)+1,length(p.str_text)-instr(p.str_text, ':',1,3)) AS KM4 from ( --第一次拆分按分号';'分隔的字符串 select distinct regexp_substr(pp.str_text, '[^;]+', 1, level) as str_text from fine_report_temp pp where pp.data_type='1' connect by regexp_substr(pp.str_text, '[^;]+', 1, level) is not null ) p
结果如下: