在 Oracle 中反转这条路径 z/y/x 到 x/y/z [英] Reverse in Oracle this path z/y/x to 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屋！