STRING SPLIT: примеры (SQL)

Простые примеры использования STRING_SPLIT

Базовое разбиение

Разделение строки по запятой.

SELECT value FROM STRING_SPLIT('яблоко,апельсин,банан', ',');

value
-------
яблоко
апельсин
банан

Использование флага enable_ordinal

Разделение с выводом порядковых номеров элементов.

SELECT value, ordinal FROM STRING_SPLIT('один,два,три', ',', 1);

value ordinal
----- -------
один  1
два   2
три   3

Разбиение с другим разделителем

Использование точки с запятой в качестве разделителя.

SELECT value FROM STRING_SPLIT('Москва;Санкт-Петербург;Новосибирск', ';');

value
---------------
Москва
Санкт-Петербург
Новосибирск

Похожие функции в MS SQL Server

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

XML-метод

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

DECLARE @str NVARCHAR(100) = '100,200,300';
SELECT Split.a.value('.', 'NVARCHAR(100)') AS value
FROM (
    SELECT CAST('' + REPLACE(@str, ',', '') + '' AS XML) AS String
) AS A
CROSS APPLY String.nodes('/x') AS Split(a);

Функция OPENJSON

Применение для строк в формате JSON. OPENJSON эффективен, если данные представлены как JSON-массив.

SELECT value FROM OPENJSON('["Красный","Зеленый","Синий"]');

Выбор метода зависит от версии SQL Server, структуры данных и требований к производительности. STRING_SPLIT рекомендуется для простого разбиения по разделителю.

Аналоги функции в других СУБД и языках

MySQL

Функция SUBSTRING_INDEX извлекает часть строки до заданного количества разделителей.

SELECT SUBSTRING_INDEX('www.example.com', '.', 2);

www.example

Для полного разбиения используют рекурсивные запросы или хранимые процедуры.

Oracle

Применяют REGEXP_SUBSTR в сочетании с иерархическими запросами.

SELECT REGEXP_SUBSTR('A,B,C,D', '[^,]+', 1, LEVEL) AS value
FROM DUAL
CONNECT BY REGEXP_SUBSTR('A,B,C,D', '[^,]+', 1, LEVEL) IS NOT NULL;

PostgreSQL

Используют функцию string_to_array, а затем unnest.

SELECT unnest(string_to_array('один:два:три', ':'));

SQLite

Отсутствует встроенная функция. Часто применяют рекурсивное CTE или сторонние расширения.

Частые ошибки при применении STRING_SPLIT

Ожидание гарантированного порядка

Строки в результате могут идти в произвольном порядке, если не используется флаг enable_ordinal.

-- Порядок не гарантирован
SELECT value FROM STRING_SPLIT('10,20,30', ',');
-- Может вернуть 30, 10, 20

Использование NULL в качестве разделителя

Разделитель не может быть NULL, это вызывает ошибку.

SELECT value FROM STRING_SPLIT('текст', NULL);
-- Ошибка: The separator parameter of the STRING_SPLIT function cannot be NULL.

Многосимвольный разделитель в старых версиях

В SQL Server до 2022 года разделитель должен быть одним символом.

-- В SQL Server 2019 вызовет ошибку
SELECT value FROM STRING_SPLIT('один--два', '--');

Изменения в последних версиях MS SQL Server

Основные нововведения появились в SQL Server 2022.

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

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

SELECT value FROM STRING_SPLIT('один--два--три', '--');

Добавление столбца ordinal

Новый необязательный параметр enable_ordinal позволяет включить столбец с порядковыми номерами элементов. Это обеспечивает детерминированный порядок строк в результате.

SELECT value, ordinal FROM STRING_SPLIT('A,B,C', ',', 1);

Расширенные сценарии работы со STRING_SPLIT

Фильтрация и соединение с другими таблицами

Использование результата разбиения в условии INNER JOIN.

DECLARE @ids VARCHAR(100) = '1,3,5';
SELECT p.*
FROM Products p
INNER JOIN STRING_SPLIT(@ids, ',') s ON p.ProductID = s.value;

Агрегация данных после разбиения

Подсчет количества элементов в каждой строке.

SELECT 
    original_string,
    COUNT(value) as element_count
FROM (VALUES 
    ('a,b,c'), 
    ('x,y')
) AS t(original_string)
CROSS APPLY STRING_SPLIT(original_string, ',') 
GROUP BY original_string;

Обработка нескольких строк с использованием CROSS APPLY

Разбиение строк, хранящихся в разных записях таблицы.

CREATE TABLE #Temp (id INT, list NVARCHAR(100));
INSERT INTO #Temp VALUES (1, 'кошка,собака'), (2, 'дом,машина');
SELECT id, value AS item
FROM #Temp
CROSS APPLY STRING_SPLIT(list, ',');

id item
-- ------
1  кошка
1  собака
2  дом
2  машина

Удаление пустых элементов

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

SELECT value 
FROM STRING_SPLIT('один,,,два,', ',') 
WHERE value <> '';