na9ort Jul 26 2018 at 09:42

Разбиение строки по разделителю. Немного про CONNECT BY

5 min

58K

Oracle*SQL*

From sandbox

Comments 9

coperius Jul 26 2018 at 10:33

Использую обычно чуть другой вариант

SELECT regexp_substr(masks, '[^,]+', 1, level) val from dual
              CONNECT BY instr(masks, ',', 1, level - 1) > 0

GlukKazan Jul 26 2018 at 10:42

Хорошая гимнастика для ума, конечно, но ещё более отличный пример того как делать не надо. Чем (в итоге) цикл-то не устроил?

na9ort Jul 26 2018 at 11:42

Я посоветовался с коллегой, и мы решили, что в нашем конкретном случае скорость выполнения не критична. Поэтому я оставил вариант с CONNECT BY и регулярными выражениями, так как читаемость лучше. В отдельную функцию выносить не стал, так как разбор больших последовательностей не так часто встречается.
Если мне требуется просто вычленить 2-3 значения из строки, то обычно я пользуюсь регулярными выражениями:

v_str := '02/2018/AAAA' || '/';
v_strA := regexp_substr(v_str, '[^/]+', 1, 1);
v_strB := regexp_substr(v_str, '[^/]+', 1, 2);
v_strC := regexp_substr(v_str, '[^/]+', 1, 3);

Отвечая конкретно на Ваш вопрос «Чем цикл не устроил?», цикл меня устраивал, просто хотелось сделать более простым способом. Не ожидал, что этот «простой» способ окажется настолько медленнее.

GlukKazan Jul 26 2018 at 11:54

Вы серьёзно думаете, что connect by с регулярками проще и понятнее для последующего сопровождения чем банальный парсинг в цикле? Далеко не всегда более лаконичный вариант проще для понимания. KISS.

pavelerosh Jul 26 2018 at 11:12

Раньше тоже использовал такой способ, но недавно в недрах Apexa был найден пакет, который работает заметно быстрее.
Сначала создаются типы:

create or replace type TypeObject as object
(
 val varchar2(4000)
);
create or replace type TypeList as table of TypeObject;

Затем функция:

create or replace function F_EXT_SUBSTR(str in clob,ch in varchar2) return TypeList pipelined is
v_path APEX_APPLICATION_GLOBAL.VC_ARR2;
BEGIN
    v_path := APEX_UTIL.STRING_TO_TABLE(str, ch);
     for i in v_path.first..v_path.last loop
      pipe row (TypeObject(v_path(i)));
    end loop;
    exception when others then
      null;
end;

И потом очень удобно использовать в запросах:

select val from table(f_ext_substr('100,200,300,400,500',','))

na9ort Jul 26 2018 at 11:12

Оо, спасибо.
Оказывается в Oracle всё таки есть функция, разбивающая строку на подстроки, хоть и возвращает она коллекцию.

Конкретно Ваш вариант мне не совсем подходит, так как у нас правило: не создавать лишний раз коллекцию на уровне схемы.
Насколько я знаю функция TABLE() работает только с объектами, объявленными на уровне схемы.

pavelerosh Jul 26 2018 at 11:17

да, TABLE() весьма прихотлив к объектам.
Я думаю, для такой функции стоит сделать исключение, так как она используется очень часто

x893 Jul 26 2018 at 11:34

Можно не менять v_str
v_str := SUBSTR(v_str, INSTR(v_str, ',', 1) + 1);
а изменять start_position в INSTR

martin_wanderer Jul 27 2018 at 09:33

У Вас лишний nvl и проверка на неравенство в connect by. Достаточно так:

CONNECT BY regexp_instr(str, '[^,]+', 1, level) > 0

Show the best of all time