CODEWITHSUNDEEP
sqloracle-databaseplsql
Jean-Philippe Martin
Jean-Philippe Martin

Reputation: 911

Reverse in Oracle this path z/y/x to x/y/z

How would I do in a SELECT query to reverse this path :

z/y/x

for 

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 ...

Upvotes: 5

Views: 10740

Answers (6)

Alok P
Alok P

Reputation: 41

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 ancestors in the lineage first, since you are starting with the childid

Upvotes: 4

tobwoerk
tobwoerk

Reputation: 128

Found another solution here that seems flexible, lean 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

Upvotes: 3

Lalith
Lalith

Reputation: 13

@Jean-Philippe Martin @OMG Ponies

Try this 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 :-)

Upvotes: 0

knittl
knittl

Reputation: 265291

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

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

Upvotes: 11

Vincent Malgrat
Vincent Malgrat

Reputation: 67732

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

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

Upvotes: 5

shahkalpesh
shahkalpesh

Reputation: 33474

Are you looking for REVERSE?
i.e

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

Upvotes: 2

Related Questions