By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
WITH
test AS (
SELECT 'проул.' as code, 'проул.|пр-к|' as str FROM dual UNION
SELECT 'ул.' as code, 'ул.|улица|ул-ца|' as str FROM dual UNION
SELECT 'пер.' as code, 'пер.|переулок|' as str FROM dual UNION
SELECT 'кв-л' as code, 'кв-л|квартал|' as str FROM dual UNION
SELECT 'линия' as code, 'линия|' as str FROM dual UNION
SELECT 'парк' as code, 'парк|' as str FROM dual UNION
SELECT 'рзд' as code, 'рзд|разъезд|' as str FROM dual UNION
SELECT 'сад' as code, 'Сад|' as str FROM dual UNION
SELECT 'тракт' as code, 'тракт|' as str FROM dual UNION
SELECT 'тер' as code, 'тер|территория|' as str FROM dual UNION
SELECT 'ст' as code, 'ст|станция|' as str FROM dual UNION
SELECT 'сл' as code, 'сл|слобода|' as str FROM dual UNION
SELECT 'пр-кт' as code, 'пр.|проспект|пр-кт|' as str FROM dual UNION
SELECT 'шоссе' as code, 'ш.|шоссе|' as str FROM dual UNION
SELECT 'пл' as code, 'пл|площадь|пл-дь|' as str FROM dual UNION
SELECT 'наб.' as code, 'наб.|набережная|наб-ая|' as str FROM dual UNION
SELECT 'тупик' as code, 'тупик|' as str FROM dual UNION
SELECT 'мост' as code, 'мост|' as str FROM dual UNION
SELECT 'пр.' as code, 'проезд|пр.|' as str FROM dual UNION
SELECT 'блв.' as code, 'блв.|бульвар|' as str FROM dual UNION
SELECT 'мкн.' as code, 'мкн.|микрорайон|' as str FROM dual UNION
SELECT 'пос.' as code, 'пос.|посёлок|' as str FROM dual UNION
SELECT 'овраг' as code, 'овраг|' as str FROM dual UNION
SELECT 'городок' as code, 'городок|' as str FROM dual
)
SELECT t1.code, SUBSTR(t1.str,
DECODE(t2.lvl, 1, 1, INSTR(t1.str, '|', 1, t2.lvl - 1) + 1),
INSTR(t1.str, '|', 1, t2.lvl)
- DECODE(t2.lvl, 1, 1, INSTR(t1.str, '|', 1, t2.lvl - 1) + 1))
FROM test t1,
LATERAL (SELECT level AS lvl FROM dual
CONNECT BY NVL(INSTR(t1.str, '|', 1, level), 0) > 0) t2
;
ORA-00933: SQL command not properly ended
alter session set events '22829 trace name context forever';
WITH
test AS (
SELECT 'проул.' as code, 'проул.|пр-к|' as str FROM dual UNION
SELECT 'ул.' as code, 'ул.|улица|ул-ца|' as str FROM dual UNION
SELECT 'пер.' as code, 'пер.|переулок|' as str FROM dual UNION
SELECT 'кв-л' as code, 'кв-л|квартал|' as str FROM dual UNION
SELECT 'линия' as code, 'линия|' as str FROM dual UNION
SELECT 'парк' as code, 'парк|' as str FROM dual UNION
SELECT 'рзд' as code, 'рзд|разъезд|' as str FROM dual UNION
SELECT 'сад' as code, 'Сад|' as str FROM dual UNION
SELECT 'тракт' as code, 'тракт|' as str FROM dual UNION
SELECT 'тер' as code, 'тер|территория|' as str FROM dual UNION
SELECT 'ст' as code, 'ст|станция|' as str FROM dual UNION
SELECT 'сл' as code, 'сл|слобода|' as str FROM dual UNION
SELECT 'пр-кт' as code, 'пр.|проспект|пр-кт|' as str FROM dual UNION
SELECT 'шоссе' as code, 'ш.|шоссе|' as str FROM dual UNION
SELECT 'пл' as code, 'пл|площадь|пл-дь|' as str FROM dual UNION
SELECT 'наб.' as code, 'наб.|набережная|наб-ая|' as str FROM dual UNION
SELECT 'тупик' as code, 'тупик|' as str FROM dual UNION
SELECT 'мост' as code, 'мост|' as str FROM dual UNION
SELECT 'пр.' as code, 'проезд|пр.|' as str FROM dual UNION
SELECT 'блв.' as code, 'блв.|бульвар|' as str FROM dual UNION
SELECT 'мкн.' as code, 'мкн.|микрорайон|' as str FROM dual UNION
SELECT 'пос.' as code, 'пос.|посёлок|' as str FROM dual UNION
SELECT 'овраг' as code, 'овраг|' as str FROM dual UNION
SELECT 'городок' as code, 'городок|' as str FROM dual
)
SELECT t1.code, SUBSTR(t1.str,
DECODE(t2.lvl, 1, 1, INSTR(t1.str, '|', 1, t2.lvl - 1) + 1),
INSTR(t1.str, '|', 1, t2.lvl)
- DECODE(t2.lvl, 1, 1, INSTR(t1.str, '|', 1, t2.lvl - 1) + 1))
FROM test t1,
LATERAL (SELECT level AS lvl FROM dual
CONNECT BY NVL(INSTR(t1.str, '|', 1, level), 0) > 0) t2
;
| CODE | SUBSTR(T1.STR,DECODE(T2.LVL,1,1,INSTR(T1.STR,'|',1,T2.LVL-1)+1),INSTR(T1.STR,'|',1,T2.LVL)-DECODE(T2.LVL,1,1,INSTR(T1.STR,'|',1,T2.LVL-1)+1)) |
|---|---|
| блв. | блв. |
| блв. | бульвар |
| городок | городок |
| кв-л | кв-л |
| кв-л | квартал |
| линия | линия |
| мкн. | мкн. |
| мкн. | микрорайон |
| мост | мост |
| наб. | наб. |
| наб. | набережная |
| наб. | наб-ая |
| овраг | овраг |
| парк | парк |
| пер. | пер. |
| пер. | переулок |
| пл | пл |
| пл | площадь |
| пл | пл-дь |
| пос. | пос. |
| пос. | посёлок |
| пр-кт | пр. |
| пр-кт | проспект |
| пр-кт | пр-кт |
| пр. | проезд |
| пр. | пр. |
| проул. | проул. |
| проул. | пр-к |
| рзд | рзд |
| рзд | разъезд |
| сад | Сад |
| сл | сл |
| сл | слобода |
| ст | ст |
| ст | станция |
| тер | тер |
| тер | территория |
| тракт | тракт |
| тупик | тупик |
| ул. | ул. |
| ул. | улица |
| ул. | ул-ца |
| шоссе | ш. |
| шоссе | шоссе |