Как распарсить строку в sql

STRING_SPLIT (Transact-SQL)

STRING_SPLIT — это табличное значение функция, которая разбивает строку на строки подстроек на основе указанного символа разделителя.

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

STRING_SPLIT требует, чтобы уровень совместимости был не менее 130. Если уровень меньше 130, ядро СУБД не удается найти функцию STRING_SPLIT .

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

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

Синтаксис

STRING_SPLIT ( string , separator [ , enable_ordinal ] ) 

Аргументы

string

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

separator

enable_ordinal

Область применения: База данных SQL Azure, Управляемый экземпляр SQL Azure, Azure Synapse Analytics (только бессерверный пул SQL) и SQL Server 2022 (16.x) и более поздних версий

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

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

Если выходной 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 должен быть константным значением, а не столбцом или переменной. Он также должен быть битом или типом данных 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 FROM STRING_SPLIT('Lorem ipsum dolor sit amet.', ' '); 

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

значение
Lorem
ipsum
dolor
sit
amet.

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

SELECT * FROM STRING_SPLIT('Lorem ipsum dolor sit amet.', ' ', 1); 

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

значение	ordinal
Lorem	1
ipsum	2
dolor	3
sit	4
amet.	5

Примеры

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

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

DECLARE @tags NVARCHAR(400) = 'clothing,road,,touring,bike' SELECT value FROM STRING_SPLIT(@tags, ',') WHERE RTRIM(value) <> ''; 

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

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

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

ИД продукта	Имя.	Теги
1	Full-Finger Gloves	clothing,road,touring,bike
2	LL Headset	bike
3	HL Mountain Frame	bike,mountain

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

SELECT ProductId, Name, value FROM Product CROSS APPLY STRING_SPLIT(Tags, ','); 

ИД продукта	Имя.	Значение
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

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

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

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

SELECT value as tag, COUNT(*) AS [number_of_articles] FROM Product CROSS APPLY STRING_SPLIT(Tags, ',') GROUP BY value HAVING COUNT(*) > 2 ORDER BY COUNT(*) DESC; 

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

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

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

SELECT ProductId, Name, Tags FROM Product WHERE 'clothing' IN (SELECT value FROM STRING_SPLIT(Tags, ',')); 

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

SELECT ProductId, Name, Tags FROM Product WHERE EXISTS (SELECT * FROM STRING_SPLIT(Tags, ',') WHERE value IN ('clothing', 'road')); 

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

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

SELECT ProductId, Name, Tags FROM Product JOIN STRING_SPLIT('1,2,3',',') ON value = ProductId; 

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

SELECT ProductId, Name, Tags FROM Product WHERE ',1,2,3,' LIKE '%,' + CAST(ProductId AS VARCHAR(20)) + ',%'; 

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

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

SELECT * FROM STRING_SPLIT('Austin,Texas,Seattle,Washington,Denver,Colorado', ',', 1) WHERE ordinal % 2 = 0; 

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

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

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

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

SELECT * FROM STRING_SPLIT('E-D-C-B-A', '-', 1) ORDER BY ordinal DESC; 

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

значение	ordinal
А	5
Б	4
О	3
D	2
E	1

Связанный контент

• LEFT (Transact-SQL)
• LTRIM (Transact-SQL)
• RIGHT (Transact-SQL)
• RTRIM (Transact-SQL)
• SUBSTRING (Transact-SQL)
• TRIM (Transact-SQL)
• Строковые функции (Transact-SQL)

Обратная связь

Были ли сведения на этой странице полезными?

Как распарсить строку в sql

Для того чтобы распарсить строку в SQL, можно использовать различные функции и операторы, в зависимости от конкретных требований и формата строки.

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

SELECT SUBSTRING_INDEX('Привет, мир', ' ', 1); 

Результатом запроса будет строка Привет.

Если же требуется извлечь определенный набор символов из строки, то можно использовать функцию SUBSTRING() . Например, следующий запрос извлекает первые три символа из строки Привет:

SELECT SUBSTRING('Привет', 1, 3); 

Результатом запроса будет строка При.

Также можно использовать функцию REGEXP_SUBSTR() для извлечения определенного фрагмента строки, используя регулярные выражения. Например, следующий запрос извлекает первое слово из строки Привет, мир:

SELECT REGEXP_SUBSTR('Привет, мир', '^[^ ]+'); 

Результатом запроса будет строка Привет.

Функции разбиения и слияния строк и массивов

Разбивает строку на подстроки, используя в качестве разделителя separator . separator должен быть константной строкой из ровно одного символа. Возвращается массив выделенных подстрок. Могут выделяться пустые подстроки, если разделитель идёт в начале или в конце строки, или если идёт более одного разделителя подряд.

Синтаксис

splitByChar(separator, s) 

Аргументы

• separator — разделитель, состоящий из одного символа. String.
• s — разбиваемая строка. String.

Возвращаемые значения

Возвращает массив подстрок. Пустая подстрока, может быть возвращена, когда:

• Разделитель находится в начале или конце строки;
• Задано несколько последовательных разделителей;
• Исходная строка s пуста.

Пример

SELECT splitByChar(',', '1,2,3,abcde'); 

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

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

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

Приступим.

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

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

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

CREATE OR REPLACE FUNCTION createNumbers(v_N IN PLS_INTEGER) RETURN VARCHAR2 IS v_str VARCHAR2(4000) := '0'; too_small EXCEPTION; PRAGMA EXCEPTION_INIT(too_small, -6502); BEGIN FOR i IN 1..v_N LOOP BEGIN v_str := v_str || ',' || TO_CHAR(i); EXCEPTION WHEN too_small THEN EXIT; END; END LOOP; RETURN v_str || ','; END; 

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

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

DECLARE v_N PLS_INTEGER := 1000; v_str VARCHAR2(4000) := createNumbers(v_N => v_N); TYPE tab_number IS TABLE OF NUMBER(5); t_str tab_number := tab_number(); BEGIN WHILE v_str IS NOT NULL LOOP t_str.EXTEND; t_str(t_str.COUNT) := SUBSTR(v_str, 1, INSTR(v_str, ',', 1) - 1); v_str := SUBSTR(v_str, INSTR(v_str, ',', 1) + 1); END LOOP; FOR i IN t_str.FIRST..t_str.LAST LOOP dbms_output.put_line(t_str(i)); END LOOP; t_str.DELETE; END; 

Результат:
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. Мы его будем использовать, как номер текущей итерации.
Получается что-то такое:

SELECT SUBSTR(str, INSTR(str, ',', 1, level - 1) + 1, INSTR(str, ',', 1, level) - INSTR(str, ',', 1, level - 1) + 1)) FROM DUAL; 

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

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

SELECT SUBSTR(str, DECODE(level, 1, 1, INSTR(str, ',', 1, level - 1) + 1), INSTR(str, ',', 1, level) - DECODE(level, 1, 1, INSTR(str, ',', 1, level - 1) + 1)) FROM DUAL; 

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

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

WITH TMain AS (SELECT '100,200,300,400,500' || ',' AS str FROM DUAL) SELECT SUBSTR(str, DECODE(level, 1, 1, INSTR(str, ',', 1, level - 1) + 1), INSTR(str, ',', 1, level) - DECODE(level, 1, 1, INSTR(str, ',', 1, level - 1) + 1)) FROM TMain CONNECT BY NVL(INSTR(str, ',', 1, level), 0) <> 0; 

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

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

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

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

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

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

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

WITH TMain AS (SELECT '100,200,300,400,500' || ',' AS str FROM DUAL) SELECT regexp_substr(str, '[^,]+', 1, level) FROM TMain CONNECT BY NVL(regexp_instr(str, '[^,]+', 1, level), 0) <> 0; 

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

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

Код для оценки трёх вариантов:

DECLARE v_N PLS_INTEGER := 1000; v_str VARCHAR2(32767); v_startTime TIMESTAMP(9); v_endTime TIMESTAMP(9); TYPE tab_number IS TABLE OF NUMBER(5); t_str tab_number := tab_number(); BEGIN v_str := createNumbers(v_N => v_N); v_startTime := SYSTIMESTAMP; WHILE v_str IS NOT NULL LOOP t_str.EXTEND; t_str(t_str.COUNT) := SUBSTR(v_str, 1, INSTR(v_str, ',', 1) - 1); v_str := SUBSTR(v_str, INSTR(v_str, ',', 1) + 1); END LOOP; v_endTime := SYSTIMESTAMP; dbms_output.put_line(v_endTime - v_startTime); t_str.DELETE; /*---------------------------------------------------------*/ v_str := createNumbers(v_N => v_N); v_startTime := SYSTIMESTAMP; SELECT TO_NUMBER(SUBSTR(v_str, DECODE(level, 1, 1, INSTR(v_str, ',', 1, level - 1) + 1), INSTR(v_str, ',', 1, level) - DECODE(level, 1, 1, INSTR(v_str, ',', 1, level - 1) + 1))) BULK COLLECT INTO t_str FROM DUAL CONNECT BY NVL(INSTR(v_str, ',', 1, level), 0) <> 0; v_endTime := SYSTIMESTAMP; dbms_output.put_line(v_endTime - v_startTime); t_str.DELETE; /*---------------------------------------------------------*/ v_str := createNumbers(v_N => v_N); v_startTime := SYSTIMESTAMP; SELECT TO_NUMBER(regexp_substr(v_str, '[^,]+', 1, level)) BULK COLLECT INTO t_str FROM DUAL CONNECT BY NVL(regexp_instr(v_str, '[^,]+', 1, level), 0) <> 0; v_endTime := SYSTIMESTAMP; dbms_output.put_line(v_endTime - v_startTime); t_str.DELETE; END; 

Результаты сведены в таблицу:

Вариант разбиения/Количество чисел	10	100	1000
Цикл	0,05мс	0,15мс	1,52мс
CONNECT BY	0,18мс	0,68мс	18,1мс
CONNECT BY + регулярные выражения	0,25мс	12,1мс	1с 137мс

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

Спасибо, что прочитали!

Список использованных источников: