Oracle разбить строку по разделителю

Обновлено: 07.07.2024

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

Уровень совместимости 130

STRING_SPLIT требует уровня совместимости не ниже 130. При уровне меньше 130 SQL Server не может найти функцию STRING_SPLIT.

Сведения об изменении уровня совместимости базы данных см. в статье Просмотр или изменение уровня совместимости базы данных.

Конфигурация совместимости для STRING_SPLIT в Azure Synapse Analytics не требуется.

Синтаксис

Аргументы

строка

Выражение любого символьного типа (например, nvarchar, varchar, nchar или char).

separator язательно, количество

Отдельное выражение любого символьного типа (например, nvarchar(1) , varchar(1) , nchar(1) или char(1) ), которое используется в качестве разделителя сцепленных подстрок.

enable_ordinal

Выражение int или bit, которое служит флагом для включения или отключения выходного столбца ordinal . Значение 1 позволяет включить столбец ordinal . Если выражение enable_ordinal не указано, равно NULL или имеет значение 0, столбец ordinal будет отключен.

В настоящее время аргумент enable_ordinal и выходной столбец ordinal поддерживаются только в Базе данных SQL Azure, Управляемом экземпляре SQL Azure и Azure Synapse Analytics (только для бессерверного пула SQL).

Типы возвращаемых данных

Если выходной столбец ordinal отключен, STRING_SPLIT возвращает таблицу с одним столбцом, строки которого являются подстроками. Имя столбца — value . Возвращает значение типа nvarchar, если любой из входных аргументов имеет тип nvarchar или nchar. В противном случае возвращается значение типа varchar. Длина типа возвращаемого значения равна длине аргумента string.

Если аргументу enable_ordinal передается значение 1, то возвращается второй столбец с именем ordinal , состоящий из значений индекса (отсчитываемого от 1) каждой позиции подстроки во входной строке. Тип возвращаемого значения — bigint.

STRING_SPLIT вводит строку с разделенными подстроками и один символ для использования в качестве разделителя. При необходимости функция поддерживает третий аргумент со значением 0 или 1, который соответственно отключает или включает выходной столбец ordinal .

STRING_SPLIT выводит таблицу с одним столбцом или двумя столбцами в зависимости от аргумента enable_ordinal.

Если enable_ordinal равен NULL , не указан или имеет значение 0, STRING_SPLIT возвращает таблицу с одним столбцом, строки которой содержат подстроки. Имя выходного столбца — value .

Если enable_ordinal имеет значение 1, функция возвращает таблицу с двумя столбцами, включая столбец ordinal , состоящий из значений индекса (отсчитываемого от 1) для подстрок в исходной входной строке.

Обратите внимание, что аргумент enable_ordinal должен иметь константное значение, а не столбец или переменную. Он также должен иметь тип данных bit или int со значением 0 или 1. В противном случае функция вызовет ошибку.

Выходные строки могут быть расположены в любом порядке. Порядок не обязательно совпадает с порядком подстрок во входной строке. Окончательный порядок сортировки можно переопределить с помощью предложения ORDER BY в инструкции SELECT, например ORDER BY value или ORDER BY ordinal .

Символ 0x0000 (char(0) ) не определен в параметрах сортировки Windows, и его нельзя включать в STRING_SPLIT.

Пустые строки нулевой длины присутствуют в том случае, если входная строка содержит два или несколько последовательных вхождений знака разделителя. Пустые подстроки обрабатываются так же, как и обычные подстроки. Можно отфильтровать строки, содержащие пустые подстроки, используя предложение WHERE, например WHERE value <> '' . Если входная строка равна NULL , функция STRING_SPLIT с табличным значением возвращает пустую таблицу.

Например, следующая инструкция SELECT использует символ пробела в качестве разделителя:

В пробном запуске предыдущая инструкция SELECT вернула следующую результирующую таблицу:

value
Lorem
ipsum
dolor
sit
amet.

В следующем примере демонстрируется включение столбца ordinal путем передачи 1 для необязательного третьего аргумента:

Затем эта инструкция возвращает следующую результирующую таблицу:

value	ordinal
Lorem	1
ipsum	2
dolor	3
sit	4
amet.	5

Примеры

A. Разделение строки значений с разделителями-запятыми

Следующая инструкция анализирует разделенный запятыми список значений и возвращает все непустые токены:

Функция STRING_SPLIT вернет пустую строку, если между разделителями ничего нет. Condition RTRIM(value) <> '' удаляет пустые токены.

Б. Разделение строки значений с разделителями-запятыми в столбце

Таблица Product содержит столбец с разделенным запятыми списком тегов, как показано в следующем примере:

ProductId	имя;	Теги
1	Full-Finger Gloves	clothing,road,touring,bike
2	LL Headset	bike
3	HL Mountain Frame	bike,mountain

Следующий запрос преобразовывает каждый список тегов и соединяет его с исходной строкой:

ProductId	имя;	value
1	Full-Finger Gloves	clothing
1	Full-Finger Gloves	road
1	Full-Finger Gloves	touring
1	Full-Finger Gloves	bike
2	LL Headset	bike
3	HL Mountain Frame	bike
3	HL Mountain Frame	mountain

Порядок вывода может меняться и не обязательно совпадает с порядком подстрок во входной строке.

В. Объединение по значениям

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

Г. Поиск по значению тега

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

Поиск продуктов с одним тегом (clothing):

Поиск продуктов с двумя тегами (clothing и road):

Д. Поиск строк по списку значений

Разработчикам необходимо создать запрос, который находит статьи по списку идентификаторов. Они могут использовать следующий запрос:

Предыдущее использование STRING_SPLIT является заменой распространенного антишаблона. Такой антишаблон может включать создание динамической строки SQL на прикладном уровне или в Transact-SQL. Или антишаблон может осуществляться с помощью оператора LIKE. Смотрите следующий пример инструкции SELECT.

Е. Поиск строк по порядковым значениям

Следующая инструкция позволяет найти все строки с четным значением индекса:

Приведенная выше инструкция возвращает следующую таблицу:

value	ordinal
Техас	2
Вашингтон	4
Колорадо	6

Ж. Упорядочение строк по порядковым номерам

Следующая инструкция возвращает разделенные значения подстрок входной строки и их порядковые значения, упорядоченные столбцу ordinal :

Я работаю PL/SQL разработчиком. Есть задача собирать некоторые данные для метрик, чтобы отслеживать загрузку систем. Есть некоторая функция, которая вызывается с параметром, состоящим из списка ID.

Задача заключается в следующем. Нужно разбить такую строку на элементы и записать их в целочисленную коллекцию.

Приступим.

Для начала нужны данные для работы. Напишем функцию, которая генерирует строку с числами, разделенными запятой. В функцию будем передавать целочисленный аргумент N – количество чисел в последовательности.

Мудрить не будем, последовательность сделаем с типом VARCHAR2, а не CLOB. Далее объясню, почему именно VARCHAR2.

Код функции для генерации последовательности:

Вернёмся к нашей задаче.

Первое, что приходит на ум, это сделать цикл по строке, с уменьшением длинны строки на каждой итерации. Так как по условию задачи результат нужно поместить в коллекцию, то создадим соответствующую коллекцию.

Результат:
0
1
2
…
421
422
423
…

Функция createNumber() принимает аргумент v_N = 1000. В функции createNumber() можно видеть обработку переполнения переменной v_str. Нехитрым подсчётом можно выяснить, что 4000 байт хватит для 1021 чисел. Наша 1000 без проблем влезает в этот размер.

Как видно, результат тот, который нужен был. Строка разделена.

Пусть даже в Oracle нет встроенной функции split(), как например в Java или Python, но данный вариант меня не устраивает, так как я считаю, что слишком много кода написано для такой простой задачи как разбиение строки.

На данном этапе я задумался, а можно ли разбить строку только средствами SQL? Я имею ввиду не классический SQL, а тот SQL, который предлагает Oracle.

Я вспомнил про конструкцию для построения иерархических запросов CONNECT BY.

Необязательный оператор START WITH говорит Oracle с чего начинать цикл, т.е. какая строка будет корневой. Условие может быть практически любым. Условие после CONNECT BY нужно указать обязательно. Тут надо сказать Oracle, как долго продолжать цикл.

Видно, что единственно важное условие для построения иерархического запроса – это оператор CONNECT BY, остальное «нанизывается» по мере надобности.

Также у этой конструкции есть псевдостолбец level, который возвращает уровень вложенности на текущей итерации.

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

Прежде чем писать запрос, обдумаем алгоритм обхода строки. Нужно, начиная от начала строки, отрезать некоторое количество символов, до символа разделителя. Выше я писал про псевдостолбец level. Мы его будем использовать, как номер текущей итерации.
Получается что-то такое:

Но если присмотреться, то можно увидеть, что данный алгоритм не сработает на самой первой итерации, так как третий аргумент функции INSTR() не может равняться 0.

Поэтому добавим небольшое условие с помощью функции DECODE().

Теперь самая первая итерации будет отрабатывать корректно.

Пора бы применить конструкцию CONNECT BY. Плюс вынесем нашу строку наверх.

Я уже писал, что при правильном условии конструкция CONNECT BY сможет вести себя подобно циклу. Условие выполняется до тех пор, пока функция INSTR() может найти n-ую позицию символа разделителя, где n – это номер текущей итерации, а как мы помним за номер итерации отвечает псевдостолбец level.

Вроде бы задача решена? Нет.

Код может и работает, но его читаемость нулевая. Я уже думал вернуться к варианту с циклом, но придумал как улучшить вариант с CONNECT BY.

В Oracle есть такое мощное средство, как регулярные выражения. Конкретно функции regexp_instr() и regexp_substr().

regexp_instr(исходная_строка, шаблон[, начальная_позиция [, вхождение ] ]) — функция возвращает позицию символа, находящегося в начале или конце соответствия для шаблона, так же как и ее аналог INSTR().

regexp_substr(исходная_строка, шаблон[, позиция [, вхождение ]]) — функция возвращает подстроку, которая соответствует шаблону.

Перепишем запрос, используя регулярные выражения:

Код читается намного лучше, чем в предыдущем примере. Такой вариант меня устраивает.

В конце было бы логичным привести сравнения времени выполнения разбора строки для трёх вариантов. Выше я обещал объяснить, почему вместо типа CLOB будем использовать тип VARCHAR2. Это нужно как раз для сравнения времени выполнения. Так как Oracle обрабатывает тип CLOB по-другому, чем VARCHAR2, что может исказить результаты.

Я знаю, что на этот вопрос в какой-то степени ответили PHP и MYSQL, но мне было интересно, может ли кто-нибудь научить меня простейшему подходу к разделению строки (с разделителями-запятыми) на несколько строк в Oracle 10g (предпочтительно) и 11g.

Таблица выглядит следующим образом:

Я хочу создать следующее:

Я видел несколько потенциальных решений для стека, однако они учитывали только один столбец (являющийся строкой, разделенной запятыми). Любая помощь будет принята с благодарностью.

Для примеров использования REGEXP , XMLTABLE и MODEL п, см Split , разделенных запятыми строк в таблице с помощью Oracle SQL

Это может быть улучшенный способ (также с regexp и connect by):

РЕДАКТИРОВАТЬ : Вот простое (например, «не углубленное») объяснение запроса.

length (regexp_replace(t.error, '[^,]+')) + 1 используется regexp_replace для удаления всего, что не является разделителем (в данном случае запятой), и length +1 для получения количества элементов (ошибок).

select level from dual connect by level <= (. ) Использует иерархический запрос , чтобы создать столбец с увеличением числа совпадений найдено, от 1 до общего числа ошибок.

В cast(multiset(. )) as sys.OdciNumberList Преобразует несколько семейств (один сборник для каждой строки в исходном наборе данных) в единый набор чисел, OdciNumberList.
table() Функция превращает коллекцию в результирующий.

FROM без соединения создает перекрестное соединение между вашим набором данных и мультимножеством. В результате строка в наборе данных с 4 совпадениями будет повторяться 4 раза (с увеличением числа в столбце с именем "column_value").

Некоторые ссылки на документы Oracle:

с 11g вы можете использовать regexp_count(t.error, ',') вместо length (regexp_replace(t.error, '[^,]+')) , что может принести еще одно улучшение производительности 485 секунд при "нормальном" CONNECT BY. Так 0,296 секунды. Ты жжешь! Теперь все, что мне нужно сделать, это понять, как это работает. :-) @BobJarvis добавил правку, чтобы объяснить, что он делает. Правописание / грамматические исправления приветствуются. «Принятый ответ плохо работает» - какой ответ принят в этой теме? Пожалуйста, используйте ссылки для ссылки на другой пост.

регулярные выражения - замечательная штука :)

привет, не могли бы вы объяснить мне, почему приведенный выше запрос дает повторяющиеся строки, если я не использовал отдельное ключевое слово в запросе Этот запрос нельзя использовать из-за @JagadeeshG, особенно для огромных таблиц. Причина медлительности в том, что каждая комбинация Name s связана, что можно увидеть, если удалить distinct . К сожалению добавление and Name = prior Name в connect by пункт причин ORA-01436: CONNECT BY loop in user data . Вы можете избежать ORA-01436 ошибки, добавив AND name = PRIOR name (или любой другой первичный ключ) и AND PRIOR SYS_GUID() IS NOT NULL

Есть огромная разница между двумя нижеприведенными:

разделение одной строки с разделителями
разделение строк с разделителями на несколько строк в таблице.

Если вы не ограничиваете строки, то предложение CONNECT BY создаст несколько строк и не даст желаемого результата.

Для одиночной строки с разделителями см. Разделить одну строку с разделителями-запятыми на строки
Чтобы разделить строки с разделителями в таблице, см. Раздел Разделить строки с разделителями-запятыми в таблице.

Помимо регулярных выражений , есть еще несколько альтернатив:

Настроить

Использование XMLTABLE :

Использование предложения MODEL :

Решение XMLTABLE по какой-то причине постоянно не выводит последнюю запись для строк смешанной длины. Например. row1: 3 слова; row2: 2 слова, row3: 1 слово; row4: 2 слова, row5: 1 слово - последнее слово не выводится. Порядок строк не имеет значения.

Еще пара примеров того же:

Имейте в виду, что comma_to_table() работает только с токенами, которые соответствуют соглашениям об именах объектов базы данных Oracle. Он будет швырять веревку, '123,456,789' например.

Я хотел бы предложить другой подход с использованием табличной функции PIPELINED. Это несколько похоже на метод XMLTABLE, за исключением того, что вы предоставляете свою собственную функцию для разделения строки символов:

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

Вы можете увидеть эту оценку оптимизатора, запустив EXPLAIN PLAN для запроса выше:

Несмотря на то, что в коллекции всего 3 значения, оптимизатор оценил для нее 8168 строк (значение по умолчанию). Сначала это может показаться несущественным, но оптимизатору этого может быть достаточно, чтобы выбрать неоптимальный план.

Решение состоит в том, чтобы использовать расширения оптимизатора для предоставления статистики для коллекции:

Тестирование полученного плана выполнения:

Как видите, количество элементов в приведенном выше плане больше не является предполагаемым значением 8196. Это по-прежнему неправильно, потому что мы передаем функции столбец вместо строкового литерала.

Для более точной оценки в данном конкретном случае потребуется некоторая настройка кода функции, но я думаю, что здесь в значительной степени объясняется общая концепция.

Я пытаюсь разбить строку с помощью regexp_subtr, но я не могу заставить ее работать.

Итак, во-первых, у меня есть этот запрос

который очень красиво извлекает мой разделитель - пустой пробел

Но тогда, когда я пытаюсь разделить строку с этой опцией, она просто не работает.

Запрос ничего не возвращает.

Помощь будет очень признательна! благодаря

Настройка схемы Oracle 11g R2:

Запрос 1:

ответил(а) 2016-05-06T20:00:00+03:00 5 лет, 6 месяцев назад

Небольшое улучшение ответа MT0. Динамический счет с использованием regexp_count и доказывает, что он обрабатывает нули, где формат [^ разделителя] + как шаблон НЕ обрабатывает элементы списка NULL. Подробнее об этом здесь: Разделить запятую отдельные значения на столбцы

Попытка отрицать строку соответствия '[[:space:]]-[[:space:]]' , поместив ее в класс символов с обводным (^), чтобы свести на нет, это не сработает. Все, что находится между двумя квадратными скобками, рассматривается как список необязательных одиночных символов, кроме названных именованных классов символов, которые расширяются до списка необязательных символов, однако из-за того, что гнездо классов символов очень вероятно, что ваши внешние скобки интерпретируется следующим образом:

[^[[:space:]] Один символ без пробела без квадратных квадратов - за ним следует один дефис [[:space:]] за которым следует один пробел ]+ за которым следуют 1 или более замыкающих квадратных скобок.

Может быть проще преобразовать ваш многосимвольный разделитель в один символ с помощью regexp_replace, а затем использовать regex_substr для поиска отдельных элементов:

В этом коде я сначала изменил - на chr(11) . Это символ ASCII вертикальной вкладки (VT), который вряд ли появится в большинстве текстовых строк. Затем выражение match выражения regexp_substr соответствует всем символам без VT, за которыми следует либо символ VT, либо конец строки. Возвращаются только символы не VT (первое подвыражение).

Если я правильно понял, это поможет вам. В настоящее время вы получаете выход как Helloworld (с пространством в конце). Поэтому я предполагаю, что вы не хотите иметь место в конце. Если это так, вы можете просто использовать пробел в разделителе.

Как вы упомянули в ур комментарий, если вы хотите, чтобы два столбца Helloworld с Helloworld и test! , вы можете сделать следующее.

Я знаю, что на этот вопрос в некоторой степени ответили PHP и MYSQL, но мне было интересно, может ли кто-нибудь научить меня простейшему подходу к разбиению строки (через запятую) на несколько строк в Oracle 10g (предпочтительно) и 11g.

Таблица выглядит следующим образом:

Я хочу создать следующее:

Я видел несколько потенциальных решений для стека, однако они учитывали только один столбец (являющийся строкой, разделенной запятой). Любая помощь будет принята с благодарностью.

Примеры использования предложений REGEXP , XMLTABLE и MODEL см. В разделе « Разделение разделенных запятыми строк в таблице с использованием Oracle SQL».

Принятый ответ имеет низкую производительность при использовании больших наборов данных.

Это может быть улучшенным способом (также с регулярным выражением и соединением):

РЕДАКТИРОВАТЬ: Вот простое (как в "не в глубине") объяснение запроса.

length (regexp_replace(t.error, '[^,]+')) + 1 использует regexp_replace для удаления всего, что не является разделителем (в данном случае запятой), и length +1 для получения количества элементов (ошибок),

Уровень select level from dual connect by level <= (. ) использует иерархический запрос для создания столбца с растущим числом найденных совпадений, от 1 до общего количества ошибок.

Приведение cast(multiset(. )) as sys.OdciNumberList преобразует несколько коллекций (по одной коллекции для каждой строки в исходном наборе данных) в одну коллекцию чисел OdciNumberList.
Функция table() преобразует коллекцию в набор результатов.

FROM без объединения создает перекрестное соединение между вашим набором данных и мультимножеством. В результате строка в наборе данных с 4 совпадениями будет повторяться 4 раза (с возрастающим номером в столбце с именем "column_value").

Некоторые ссылки на документы Oracle:

485 секунд с «нормальным» CONNECT BY. 0,296 секунды таким образом. Ты жжешь! Теперь все, что мне нужно сделать, это понять, как это работает. :-) @BobJarvis добавил редактирование, чтобы объяснить, что он делает. Исправления орфографии / грамматики приветствуются.

регулярные выражения - замечательная вещь:)

Привет, пожалуйста, объясните мне, почему вышеупомянутый запрос дает повторяющиеся строки, если я не использовал отдельное ключевое слово в запросе Этот запрос неприменим из-за @JagadeeshG, особенно для больших таблиц.

Существует огромное различие между двумя ниже:

разделение отдельной строки с разделителями
разделение строк с разделителями для нескольких строк в таблице.

Для строки с одиночным ограничением смотрите Разделить строку с разделителями-запятыми в строки
Для разделения строк с разделителями в таблице просмотрите Разделите строки с разделителями-запятыми в таблице

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

Настройка

Использование XMLTABLE:

Использование МОДЕЛЬ:

+1 за несколько подходов и очень четкое объяснение! И вы просто действительно помогли мне с проблемой, которую я не мог решить, используя всего несколько строк SQL Большие пальцы за творчество и ясность! Смотрите мой ответ для альтернативной реализации XMLTABLE. Решение XMLTABLE по какой-то причине постоянно не может вывести последнюю запись для строк смешанной длины. Например. строка 1: 3 слова; строка 2: 2 слова, строка 3: 1 слово; строка 4: 2 слова, строка 5: 1 слово - не будет выводить последнее слово. Порядок строк не имеет значения.

Несколько других примеров:

Я хотел бы предложить другой подход, используя функцию таблицы PIPELINED. Это несколько похоже на технику XMLTABLE, за исключением того, что вы предоставляете свою собственную настраиваемую функцию для разделения символьной строки:

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

Вы можете увидеть эту оценку оптимизатора, запустив EXPLAIN PLAN по указанному выше запросу:

Несмотря на то, что коллекция имеет только 3 значения, оптимизатор оценил 8168 строк для нее (значение по умолчанию). Вначале это может показаться неуместным, но оптимизатору может быть достаточно, чтобы принять решение о субоптимальном плане.

Проверка итогового плана выполнения:

Как вы можете видеть, мощность на плане выше не является 8196 угаданными значение больше. Это все еще неверно, потому что мы передаем столбцу вместо строкового литерала в функцию.

Некоторая настройка на функциональный код будет необходима, чтобы дать более близкую оценку в этом конкретном случае, но я думаю, что общая концепция здесь в значительной степени объясняется.

Читайте также: