在Oracle中将此路径z/y/x反转为x/y/z [英] Reverse in Oracle this path z/y/x to x/y/z
本文介绍了在Oracle中将此路径z/y/x反转为x/y/z的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我将如何在SELECT查询中反转此路径:
How would I do in a SELECT query to reverse this path :
z/y/x
对于
x/y/z
其中/是分隔符 并且一行中可以有很多定界符
where / is the delimiter and where there can be many delimiters in a single line
ex: select (... z/y/x/w/v/u ...) reversed_path from ...
推荐答案
最简单的方法可能是编写存储的pl/sql函数,但是可以单独使用SQL(Oracle)完成.
The simplest way would probably be to write a stored pl/sql function, however it can be done with SQL (Oracle) alone.
这将分解子路径中的路径:
This will decompose the path in subpath:
SQL> variable path varchar2(4000);
SQL> exec :path := 'a/b/c/def';
PL/SQL procedure successfully completed
SQL> SELECT regexp_substr(:path, '[^/]+', 1, ROWNUM) sub_path, ROWNUM rk
2 FROM dual
3 CONNECT BY LEVEL <= length(regexp_replace(:path, '[^/]', '')) + 1;
SUB_P RK
----- --
a 1
b 2
c 3
def 4
然后我们用sys_connect_by_path
重新构成反向路径:
We then recompose the reversed path with the sys_connect_by_path
:
SQL> SELECT MAX(sys_connect_by_path(sub_path, '/')) reversed_path
2 FROM (SELECT regexp_substr(:path, '[^/]+', 1, ROWNUM) sub_path,
3 ROWNUM rk
4 FROM dual
5 CONNECT BY LEVEL <= length(regexp_replace(:path, '[^/]', '')) + 1)
6 CONNECT BY PRIOR rk = rk + 1
7 START WITH rk = length(regexp_replace(:path, '[^/]', '')) + 1;
REVERSED_PATH
-------------
/def/c/b/a
这篇关于在Oracle中将此路径z/y/x反转为x/y/z的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文