with test as ( select 'A;BB;CCC;DDDD;EEEEE#1;2;3;4;5' as str from dual ) select trim(both '|' from listagg(str) within group( order by 1)) as sultstr from (select regexp_substr(str1, '\w+', 1, level) || '^' || regexp_substr(str2, '\d+', 1, level) || '|' as str from (select replace(regexp_substr(str, '.+#'), '#', '') as str1, replace(regexp_substr(str, '#.+'), '#', '') as str2 from test) connect by level <= 5) ======================================== 1 A^1|BB^2|CCC^3|DDDD^4|EEEEE^5
再拼接比较好吧
参考函数:
substr
trim
select 'A;BB;CCC;DDDD;EEEEE#1;2;3;4;5' as str from dual
)
select trim(both '|' from listagg(str) within
group(
order by 1)) as sultstr
from (select regexp_substr(str1, '\w+', 1, level) || '^' ||
regexp_substr(str2, '\d+', 1, level) || '|' as str
from (select replace(regexp_substr(str, '.+#'), '#', '') as str1,
replace(regexp_substr(str, '#.+'), '#', '') as str2
from test)
connect by level <= 5)
========================================
1 A^1|BB^2|CCC^3|DDDD^4|EEEEE^5