[ACCEPTED]-Reverse in Oracle this path z/y/x to x/y/z-plsql

Accepted answer
Score: 11

You can get your result by connecting the 4 reverted components, then reverting the 3 resulting string again. Just make sure you 2 strip your starting separator and put it 1 on the other side:

SELECT '/' || REVERSE(LTRIM(SYS_CONNECT_BY_PATH(REVERSE(x), '/'), '/') AS reversed_path
...
Score: 5

The simplest way would probably be to write 4 a stored pl/sql function, however it can 3 be done with SQL (Oracle) alone.

This will 2 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

We then recompose 1 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
Score: 3
select
    listagg(n.name,'\') within group (order by level desc)
 from nodes n
start with n.id = :childid
connect by prior n.parentid = n.id
order by level desc;

order by level desc. will concatenate the 2 ancestors in the lineage first, since you 1 are starting with the childid

Score: 2

Are you looking for REVERSE?
i.e

SELECT REVERSE('z/y/x') FROM DUAL;

0

Score: 2

Found another solution here that seems flexible, lean 1 and I find quite easy to understand:

    SELECT son_id,
           dad_id,
           son_name,
           SYS_CONNECT_BY_PATH (son_name, '/') AS family_path
      FROM (    SELECT son_id,
                       dad_id,
                       son_name,
                       CONNECT_BY_ISLEAF AS cbleaf
                  FROM family
            START WITH son_id IN (1, 2, 3, 4, 5)
            CONNECT BY PRIOR dad_id = son_id)
     WHERE CONNECT_BY_ISLEAF = 1
START WITH cbleaf = 1
CONNECT BY PRIOR son_id = dad_id
Score: 0

@Jean-Philippe Martin @OMG Ponies

Try this 1 query,

SELECT REGEXP_SUBSTR(PATH,'[^/]+',1,4) || '/' || REGEXP_SUBSTR(PATH,'[^/]+',1,3) || '/' || REGEXP_SUBSTR(PATH,'[^/]+',1,2) || '/' || REGEXP_SUBSTR(PATH,'[^/]+',1,1) "Reverse of Path" 
  FROM (SELECT 'a/bc/def/ghij' PATH FROM DUAL);

This will do it I guess :-)

More Related questions