Oracle 11g Release 2

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


блв.
блв.

блв.
бульвар

городок
городок

кв-л
кв-л

кв-л
квартал

линия
линия

мкн.
мкн.

мкн.
микрорайон

мост
мост

наб.
наб.

наб.
набережная

наб.
наб-ая

овраг
овраг

парк
парк

пер.
пер.

пер.
переулок

пл
пл

пл
площадь

пл
пл-дь

пос.
пос.

пос.
посёлок

пр-кт
пр.

пр-кт
проспект

пр-кт
пр-кт

пр.
проезд

пр.
пр.

проул.
проул.

проул.
пр-к

рзд
рзд

рзд
разъезд

сад
Сад

сл
сл

сл
слобода

ст
ст

ст
станция

тер
тер

тер
территория

тракт
тракт

тупик
тупик

ул.
ул.

ул.
улица

ул.
ул-ца

шоссе
ш.

шоссе
шоссе

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))
блв.	блв.
блв.	бульвар
городок	городок
кв-л	кв-л
кв-л	квартал
линия	линия
мкн.	мкн.
мкн.	микрорайон
мост	мост
наб.	наб.
наб.	набережная
наб.	наб-ая
овраг	овраг
парк	парк
пер.	пер.
пер.	переулок
пл	пл
пл	площадь
пл	пл-дь
пос.	пос.
пос.	посёлок
пр-кт	пр.
пр-кт	проспект
пр-кт	пр-кт
пр.	проезд
пр.	пр.
проул.	проул.
проул.	пр-к
рзд	рзд
рзд	разъезд
сад	Сад
сл	сл
сл	слобода
ст	ст
ст	станция
тер	тер
тер	территория
тракт	тракт
тупик	тупик
ул.	ул.
ул.	улица
ул.	ул-ца
шоссе	ш.
шоссе	шоссе