字符截取拼接

[gz0817]

字符格式：A;BB;CCC;DDDD;EEEEE&1;2;3;4;5
处理返回格式：A^1|BB^2|CCC^3|DDDD^4|EEEEE^5
Oracle 求助。。。。

[leonskenedy]

regexp应该写不过来，弄个function自己解析下吧

[linwaterbin]

先截取，然后存于临时表 再拼接比较好吧 参考函数： substr trim

[小海葵1]

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