with tt as(select 'abc|def|ghi' a from dual union all select '123|456|789' from dual)
select substr('|'||a,2,instr(a||'|','|')-1), substr('|'||a,instr(a||'|','|')+2,instr(a||'|','|',1,2)-instr(a||'|','|')-1), substr('|'||a,instr(a||'|','|',1,2)+2,instr(a||'|','|',1,3)-instr(a||'|','|',1,2)-1) from tt10g以上可以试试 with tt as(select 'abc|def|ghi' a from dual union all select '123|456|789' from dual)
select regexp_replace(a,'(\w+)\|(\w+)\|(\w+)','\1'), regexp_replace(a,'(\w+)\|(\w+)\|(\w+)','\2'), regexp_replace(a,'(\w+)\|(\w+)\|(\w+)','\3') from tt
instr() substr() 你要用到这两个函数
with tt as(select 'abc|def|ghi' a from dual union all select '123|456|789' from dual) select regexp_replace(a,'(\w+)\|(\w+)\|(\w+)','\1'), regexp_replace(a,'(\w+)\|(\w+)\|(\w+)','\2'), regexp_replace(a,'(\w+)\|(\w+)\|(\w+)','\3') from tt
union all select '123|456|789' from dual)
select substr('|'||a,2,instr(a||'|','|')-1),
substr('|'||a,instr(a||'|','|')+2,instr(a||'|','|',1,2)-instr(a||'|','|')-1),
substr('|'||a,instr(a||'|','|',1,2)+2,instr(a||'|','|',1,3)-instr(a||'|','|',1,2)-1)
from tt10g以上可以试试
with tt as(select 'abc|def|ghi' a from dual
union all select '123|456|789' from dual)
select regexp_replace(a,'(\w+)\|(\w+)\|(\w+)','\1'),
regexp_replace(a,'(\w+)\|(\w+)\|(\w+)','\2'),
regexp_replace(a,'(\w+)\|(\w+)\|(\w+)','\3') from tt
substr()
你要用到这两个函数
union all select '123|456|789' from dual) select regexp_replace(a,'(\w+)\|(\w+)\|(\w+)','\1'),
regexp_replace(a,'(\w+)\|(\w+)\|(\w+)','\2'),
regexp_replace(a,'(\w+)\|(\w+)\|(\w+)','\3') from tt