3,491
社区成员
发帖
与我相关
我的任务
分享
with t1 as
(
select 'abc|def|ghi' a ,'ID_1' AS B from dual
union all
select '123|456|789','ID_2' from dual
),
T2 AS(
SELECT B,TRIM(REGEXP_SUBSTR(A,'[^|]+',1,LEVEL)) AS PAR1
FROM T1
CONNECT BY B = PRIOR B
AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL
AND LEVEL <= length(regexp_replace(A, '[^|]'))+1)
SELECT * FROM T2
/*
B PAR1
1 ID_1 abc
2 ID_1 def
3 ID_1 ghi
4 ID_2 123
5 ID_2 456
6 ID_2 789
*/
with tmp 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 tmp
10g以上可以试试:
with tmp 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 tmp