源表:detail
处理效果:
sql语句:
with recursive
func(f, smcl_num) as (select part, length(part) - length(replace(part, ';', ''))
from chq3g4
union
select ltrim(ltrim(f, substr(f, 1, instr(f, ';') - 1)), ';'), smcl_num - 1
from func
where smcl_num > 0),
detail as (select case
when instr(f, ';') = 0 then f
else substr(f, 1, instr(f, ';') - 1)
end col1
from func)
SELECT substr(col1, 1, instr(col1, ':')-1) AS col1,
substr(substr(col1, instr(col1, ':')+1), 1, instr(substr(col1, instr(col1, ':')+1), ':')-1) AS col2,
-- substr(substr(col1, instr(col1, ':')+1), instr(substr(col1, instr(col1, ':')+1), ':')+1) AS col31,
substr(substr(substr(col1, instr(col1, ':')+1), instr(substr(col1, instr(col1, ':')+1), ':')+1), 1, instr(substr(substr(col1, instr(col1, ':')+1), instr(substr(col1, instr(col1, ':')+1), ':')+1), ':')-1) AS col3,
substr(substr(substr(col1, instr(col1, ':')+1), instr(substr(col1, instr(col1, ':')+1), ':')+1),instr(substr(substr(col1, instr(col1, ':')+1), instr(substr(col1, instr(col1, ':')+1), ':')+1),':') + 1) AS col4
from detail;
原理:
利用现有的字符串处理函数想尽办法进行处理。