Разбиение строки на строки в Oracle: для множества столбцов

Быстрый ответ

В Oracle существует функциональнальность разделения строки на подстроки с помощью выражения CONNECT BY LEVEL. Например, чтобы разделить строку на подстроки по запятым, можно использовать следующий код:

SELECT SUBSTR(txt, INSTR(txt, ',', 1, LEVEL) + 1, INSTR(txt, ',', 1, LEVEL + 1) – INSTR(txt, ',', 1, LEVEL) – 1) AS value
FROM (SELECT 'apple,orange,banana' AS txt FROM dual)
CONNECT BY LEVEL <= LENGTH(txt) – LENGTH(REPLACE(txt, ',')) + 1
  AND PRIOR SYS_GUID() IS NOT NULL
  AND PRIOR txt IS NOT NULL;

В этом примере LEVEL позволяет отслеживать количество итераций, функции SUBSTR и INSTR отвечают за извлечение подстрок, а CONNECT BY контролирует процесс до окончательного разделения строки.

Техники для экспертов в разделении строк

Использование XMLTable для эффективного разделения

Когда стоит задача достижения высокой производительности или проблематичного разделения строк, следует использовать XMLTable. Это отличный инструмент, выступающий в роли мощного XML-парсера:

SELECT trim(column_value) as value
FROM XMLTable(('"' || REPLACE(:csv, ',', '","') || '"'));

Оператор REPLACE окружает каждую подстроку кавычками, а XMLTable преобразует эту строку в XML для последующего разделения.

Подробнее об этом расскажет наш спикер на видео

Создание функции PL/SQL для типового разделения

Если вам часто приходится разделить строки в вашей повседневной работе, интеграция этого знания в функцию PL/SQL поможет сохранить время и упростит этот процесс:

CREATE OR REPLACE FUNCTION split_string (
  p_list  IN  VARCHAR2,
  p_delim IN  VARCHAR2 := ','
) RETURN sys.OdciVarchar2List PIPELINED IS
BEGIN
    FOR i IN (
        SELECT REGEXP_SUBSTR(p_list, '[^' || p_delim || ']+', 1, LEVEL) as element
        FROM dual
        CONNECT BY REGEXP_SUBSTR(p_list, '[^' || p_delim || ']+', 1, LEVEL) IS NOT NULL
    ) LOOP
        PIPE ROW(i.element);
    END LOOP;
    RETURN;
END split_string;

Таким образом, у вас будет готовое к использованию инструмент для разделения строк.

Разделение строк с помощью MODEL

Функция MODEL дает возможность манипулировать со строками так, как если бы вы работали с таблицами в Excel. Эта функция может быть использована для разделения строк:

WITH dataset AS (
  SELECT 'Wilson,Field,Gates' AS csv FROM dual
)
SELECT value
FROM dataset
MODEL
  DIMENSION BY (0 as id)
  MEASURES (csv as value, 0 as start_pos, 0 as end_pos)
  RULES ITERATE (1000)
  (
    start_pos[ITERATION_NUMBER] = 
      CASE ITERATION_NUMBER WHEN 0 THEN 1 ELSE end_pos[ITERATION_NUMBER-1]+2 END,
    end_pos[ITERATION_NUMBER] = 
      INSTR(csv[0], ',', start_pos[ITERATION_NUMBER]),
    value[ITERATION_NUMBER] = 
      CASE 
        WHEN end_pos[ITERATION_NUMBER] > 0 THEN 
          SUBSTR(csv[0], start_pos[ITERATION_NUMBER], end_pos[ITERATION_NUMBER] – start_pos[ITERATION_NUMBER]) 
        ELSE 
          SUBSTR(csv[0], start_pos[ITERATION_NUMBER])
      END
  )
WHERE value IS NOT NULL
ORDER BY id;

Функция MODEL похожа на универсальный инструмент в контексте SQL, так как позволяет детально управлять процессом разделения строк.

Визуализация

Процесс разделения строки на строки в Oracle можно представить как отдельные вагоны поезда, нагруженные пассажирами:

Поезд: 🚂 === "Имя, Возраст, Местоположение" ===

Наша цель — разместить пассажиров (слова) в отдельные купе (строки):

SELECT REGEXP_SUBSTR(str, '[^,]+', 1, LEVEL) as Word 
FROM (SELECT 'Имя, Возраст, Местоположение' AS str FROM dual)
CONNECT BY REGEXP_SUBSTR(str, '[^,]+', 1, LEVEL) IS NOT NULL;

Результат таков:

| Ряд 1 | 🚂🪑: Имя       |
| Ряд 2 | 🚂🪑: Возраст   |
| Ряд 3 | 🚂🪑: Местоположение |

Каждая запятая становится остановкой, где пассажир выходит в новое купе.

Советы от Тома Кайта и его функция str2tbl

В качестве примера используем модифицированную версию функции str2tbl, созданную Томом Кайтом:

CREATE OR REPLACE TYPE stringlist AS TABLE OF VARCHAR2(4000);

CREATE OR REPLACE FUNCTION str2tbl(p_str IN VARCHAR2) RETURN stringlist PIPELINED
IS
    l_string       LONG := p_str || ',';
    l_comma_index  PLS_INTEGER;
    l_index        PLS_INTEGER := 1;
BEGIN
    LOOP
        l_comma_index := INSTR(l_string, ',', l_index);
        EXIT WHEN l_comma_index = 0;
        PIPE ROW(SUBSTR(l_string, l_index, l_comma_index – l_index));
        l_index := l_comma_index + 1;
    END LOOP;
    RETURN;
END str2tbl;

Эта функция эффективно разбивает строки, используя возможности PL/SQL и технологию PIPELINED.

Техники очистки данных

При работе с несколькими столбцами для сохранения контекста скрещивание объединений разделенного результата с исходными данными позволяет получить структуру:

WITH data AS (
  SELECT 1 AS id, 'Error1,Error2,Error3' AS errors FROM dual
)
SELECT d.id, 
       t.column_value AS error
FROM data d
CROSS JOIN TABLE(str2tbl(d.errors)) t;

Чтобы обеспечить чистоту данных, используйте TRIM для удаления пробелов и DISTINCT для исключения дубликатов:

SELECT DISTINCT TRIM(REGEXP_SUBSTR(str, '[^,]+', 1, LEVEL)) as Word
FROM (SELECT 'Error1, Error2, Error1, Error3' AS str FROM dual)
CONNECT BY REGEXP_SUBSTR(str, '[^,]+', 1, LEVEL) IS NOT NULL;

Очистка от пробелов и дубликатов подобно приготовлению изысканного банкета — мелкие детали важны для создания безупречной атмосферы.

Полезные материалы

1. Ask TOM: Разделение строки на строки с использованием знаков препинания — авторитет в области баз данных Том Кайт демонстрирует мастерство разделения CSV-строк.
2. mysql – SQL: разделение значений на несколько строк – Stack Overflow — коллективный интеллект сообщества Stack Overflow помогает разобраться в вопросах разделения строк в SQL.
3. Многоязычный синтаксис регулярных выражений — документация Oracle по использованию регулярных выражений в строках.
4. Иерархические запросы — глава, посвященная использованию CONNECT BY для работы с иерархическими данными, от Oracle.
5. Что значит /bin/bash^M: плохая команда интерпретатора? – Stack Overflow — полезный материал, обсуждающий проблемы переноса строки и форматирования в скриптах.