67,513
社区成员
发帖
与我相关
我的任务
分享
select substring_index('a@@b@@c', '@@', 1)
union
select right(substring_index('a@@b@@c', '@@', 2),1)
union
select right(substring_index('a@@b@@c', '@@', 3),1);
select * from ed_survey t where regexp_like(t.answer, '@@');
with splitData as(
select *
from (select s.questionnum,
s.year,
decode(sign(t1.c_count),
1,
substr(s.answer, 1, t2.c_length),
0,
null,
null) a1,
decode(sign(t1.c_count - 1),
1,
substr(s.answer, t2.c_length + 3, t2.c_length),
0,
substr(s.answer, t2.c_length + 3, t2.c_length),
null) a2,
decode(sign(t1.c_count - 2),
1,
substr(s.answer, t2.c_length + 3, t2.c_length),
0,
substr(s.answer, t2.c_length + 3, t2.c_length),
null) a3
from ed_survey s,
(select t0.questionnum,
(length(t0.answer) -
length(replace(t0.answer, '@@', null))) / 2 c_count
from ed_survey t0
where regexp_like(t0.answer, '@@')) t1,
(select t.questionnum, instr(t.answer, '@@') - 1 c_length
from ed_survey t
where regexp_like(t.answer, '@@')) t2
where s.questionnum = t2.questionnum
and s.questionnum = t1.questionnum) checks), createData as (select d.questionnum,
d.year,
d.a1 answer
from splitData d
union all
select d.questionnum,
d.year,
d.a2 answer
from splitData d
union all
select d.questionnum,
d.year,
d.a3 answer
from splitData d)
select * from createData where answer is not null;