[ACCEPTED]-Reverse in Oracle this path z/y/x to x/y/z-plsql
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
...
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
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
Are you looking for REVERSE?
i.e
SELECT REVERSE('z/y/x') FROM DUAL;
0
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
@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
We use cookies to improve the performance of the site. By staying on our site, you agree to the terms of use of cookies.