D
If the latter level is known in advance, for example, as in question 3: with region as (
select 00 reg_id, 00 preg_id, ' ' regname from dual where 1=0 union all
select 11, null, 'Ленинградская обл' from dual union all
select 21, 11, 'всеволожский р-н' from dual union all
select 31, 21, 'сертолово' from dual
)
select substr(sys_connect_by_path(regname, '->'),3) regpath
from region
where level = 3
connect by prior reg_id = preg_id
start with preg_id is null;
Leningrad oblast-of-the-all-white ra-tartolovoIf the latter level is not known in advance, it is possible, by condition, only for the leaf knots in the hierarchy:with region as (
select 00 reg_id, 00 preg_id, ' ' regname from dual where 1=0 union all
select 11, null, 'Ленинградская обл' from dual union all
select 21, 11, 'всеволожский р-н' from dual union all
select 31, 21, 'сертолово' from dual union all
select 12, null, 'Центральный ф.о.' from dual union all
select 22, 12, 'Брянская обл.' from dual union all
select 32, 22, 'Маньковский р-н' from dual union all
select 42, 32, 'Чертилково' from dual
),
regpath as (
select level,
connect_by_isleaf isleaf,
sys_connect_by_path(regname, '->') path,
'->' path_prefix
from region r
connect by prior reg_id = preg_id
start with preg_id is null
)
select regexp_replace(path, '^' || path_prefix) place
from regpath
where isleaf = 1;
Leningrad oblast-of-the-all-white ra-tartolovoCentral F.o.-Cont.Details of hierarchical requests https://docs.oracle.com/database/121/SQLRF/queries003.htm#SQLRF52332 ♪