Как корректно разбить строку на подстроки по разделителю на множестве строк (по датасету)?

Question

[Сергей c0re]

Что-то никак не соображу...

С одной строкой все просто, разбивается через CONNECT BY, например

WITH 
  test AS (
SELECT 'ул.' as code, 'ул.|улица|ул-ца|' as str FROM dual
)
SELECT t1.code, SUBSTR(t1.str, 
              DECODE(level, 1, 1, INSTR(t1.str, '|', 1, level - 1) + 1),
              INSTR(t1.str, '|', 1, level) 
              - DECODE(level, 1, 1, INSTR(t1.str, '|', 1, level - 1) + 1))
FROM test t1
CONNECT BY NVL(INSTR(t1.str, '|', 1, level), 0) <> 0
;

но никак не соображу как это сделать на множестве подобных строк, они все "перемножаются" друг на друга...

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(level, 1, 1, INSTR(t1.str, '|', 1, level - 1) + 1),
              INSTR(t1.str, '|', 1, level) 
              - DECODE(level, 1, 1, INSTR(t1.str, '|', 1, level - 1) + 1))
FROM test t1
CONNECT BY NVL(INSTR(t1.str, '|', 1, level), 0) <> 0
;

а надо чтобы было как в первом случае, для каждого code соответственно разбитые строки, а не перемножение...

PS: конечно же помогает DISTINCT, но мне почему-то кажется что это не совсем корректно ??

Answer 1

[Максим Y]

Привет.
Нужно вовремя остановить connect by.

SELECT
       t1.code
      ,regexp_substr(t1.str, '[^\|]+', 1, lvl) AS str_splitted
  FROM test t1
 CROSS JOIN LATERAL (
       SELECT level AS lvl
         FROM dual
      CONNECT BY level <= regexp_count(t1.str, '\|') 
 ) t2

Comments

[Сергей c0re]

Привет.
У меня Oracle 9i, а регулярка появилась с 10g (Извнияюсь, не уточнил в вопросе)

[Максим Y]

Ой, там и LATERAL JOIN нету кажись.
Я когда-то для подобной задачи писал pipelined table function, которая проходилась по исходной таблице (твой with блок) построчно и коллектила результат разбивки в табличный тип. Работало быстро, но это + 2-3 объекта в БД создавать.
И там прямо много строк было - distinct вешался.

Думаю, если у тебя небольшой датасет без претензий на взрывной рост, - делай с дистинктом, и пусть себе работает.
Вариант без перерасхода ресурсов - добавлять pipelined table функцию.

[Максим Y]

Сергей c0re, попробуй так:

SELECT t1.code, SUBSTR(t1.str, 
              DECODE(lvl, 1, 1, INSTR(t1.str, '|', 1, lvl - 1) + 1),
              INSTR(t1.str, '|', 1, lvl) 
              - DECODE(lvl, 1, 1, INSTR(t1.str, '|', 1, lvl - 1) + 1))
FROM test t1
,LATERAL (SELECT level AS lvl
            FROM dual
         CONNECT BY level <= LENGTH(t1.str) - LENGTH(REPLACE(t1.str, '|')))

Не могу найти, были lateral views, или нет в 9i.

[Сергей c0re]

Максим Y, ругается:
SQL Developer 4.0 ругается на синтаксис
база кидает ошибку:

ORA-00933: SQL command not properly ended

причем 11g тоже:
www.sqlfiddle.com/#!4/77be8/3

а 18C нормально работает:
https://dbfiddle.uk/?rdbms=oracle_18&fiddle=60a1c2...

:-?

[Сергей c0re]

Максим Y,

Думаю, если у тебя небольшой датасет без претензий на взрывной рост, - делай с дистинктом, и пусть себе работает.
Вариант без перерасхода ресурсов - добавлять pipelined table функцию.

датасет без претензий, максимум еще пяток значений добавить... получающийся датасет будет порядка 6-6.5к записей, дистинкт вроде не вешается.

Это будет часть запроса который выполняется "разово", для загрузки и сопоставления данных, поэтому можно немного и подождать в принципе.

Если другого выхода нет, оставлю DISTINCT, в базе для этого не охота городить функции.

PS: Думаю дело именно в наличии LATERAL - inline-представлении
интересная штука...
спс

[Сергей c0re]

Нашел... как включить LATERAL, до 12С это было типа недокументированной фичей и чтобы ее включить необходимо выполнить:

alter session set events '22829 trace name context forever';

Попробовал в 11g (на dbfiddle.uk) и сработало!

https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=3aa5...

PS: попробовал у себя на 9i и.... тоже сработало! ))

[Максим Y]

Интересно. Не часто такое пригодится, конечно) Но докопались.

Answer 2

[Сергей c0re]

Необходимо использовать LATERAL (inline-представление), как посоветовал Максим Y
Но, до версии 12С это было типа недокументированной "фичей" и чтобы ее включить необходимо выполнить:

alter session set events '22829 trace name context forever';

Попробовал в 11g (на dbfiddle.uk) и сработало!

https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=3aa5...

PS: попробовал у себя на 9i и.... тоже сработало! ))

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
;

так же есть еще вариант, через преобразование в XML:

WITH
str_csv 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
),
str_xml AS (
  SELECT code, XMLType('<a><b>'||replace(str, '|','</b><b>')||'</b></a>') xml FROM str_csv
)
SELECT
    code, extractValue(value(t),'b') str_type
  FROM
    str_xml s,
    TABLE(XMLSequence(s.xml.extract('a/b'))) t
  WHERE extractValue(value(t),'b') is not null
;