如何在SELECT查询中反转此路径：
z/y/x
对于
x/y/z
where /是分隔符
并且在一行中可以有许多分隔符
ex: select (... z/y/x/w/v/u ...) reversed_path from ...
解决方法
最简单的方法可能是编写一个存储的pl / sql函数,但是它可以单独用sql(Oracle)完成.
这将分解子路径中的路径：
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重构反向路径：
sql> SELECT MAX(sys_connect_by_path(sub_path,'/')) reversed_path 2 FROM (SELECT regexp_substr(: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
