Разделение строки в SQL Server 2008 R2: функция split

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

Для SQL Server 2016 и более поздних версий предусмотрена функция STRING_SPLIT, применяемая для разделения строки на подстроки:

SELECT value FROM STRING_SPLIT('item1,item2,item3', ',');

Данный инструмент преобразует исходную строку в индивидуальные элементы. Если вы работаете с предыдущими версиями SQL Server, придется воспользоваться комбинацией функций SUBSTRING и CHARINDEX или создать собственную функцию для выполнения данной задачи.

Альтернативы для версий до 2016 года

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

В SQL Server 2008 R2 предлагается применить XML метод:

DECLARE @Input VARCHAR(MAX) = 'item1,item2,item3';
SELECT Split.a.value('.', 'VARCHAR(100)') AS Value 
FROM (
    SELECT CAST ('<M>' + REPLACE(@Input, ',', '</M><M>') + '</M>' AS XML) AS Data
) AS A 
CROSS APPLY Data.nodes ('/M') AS Split(a);

Ваши строки, разделенные запятыми, будут преобразованы в XML и разделены на части с использованием CROSS APPLY.

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

Пользовательская функция разделения

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

CREATE FUNCTION dbo.SplitString (@Input VARCHAR(MAX), @Delimiter CHAR(1))
RETURNS @Output TABLE (Value VARCHAR(MAX))
BEGIN
  ...
  WHILE CHARINDEX(@Delimiter, @Input) > 0
  ...
END

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

SELECT Value FROM dbo.SplitString('item1,item2,item3', ',');

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

Специфические случаи и техники оптимизации

Удаление пробелов

Для удаления пробелов используйте функции LTRIM и RTRIM:

SELECT LTRIM(RTRIM(value)) AS TrimmedValue FROM STRING_SPLIT('item1 , item2 , item3', ',');

Рекурсивные CTE

Рекурсивные CTE – мощный механизм для разделения строк в SQL Server:

;WITH cte AS (...)
SELECT * FROM cte OPTION (MAXRECURSION 0);

Скорость выполнения

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

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

Рассмотрим аналогию: представьте, что каждый символ строки – это отдельный вагон поезда.

Строка-поезд: [S][p][l][i][t][t][h][i][s]

Применяя STRING_SPLIT(), мы разделяем поезд на отдельные составы:

SELECT value FROM STRING_SPLIT('Splitthis', 't');

| Платформа (Строка)  |
| ------------------- |
| Spli                |
| this                |
🚉

Символ 't' выступает в роли диспетчера, который останавливает поезд и распределяет буквы по новым платформам (строкам).

Обработка "грязных" разделителей

Функция Replace

Если вы столкнулись с «грязными» разделителями, сначала очистите их, используя функцию REPLACE:

SELECT value FROM STRING_SPLIT(REPLACE('item1|item2|item3', '|', ','), ',');

XML метод

При использовании XML метода достаточно заменить разделитель в функции REPLACE:

DECLARE @Input VARCHAR(MAX) = 'item1|item2|item3';
SELECT Split.a.value('.', 'VARCHAR(100)') AS Value 
FROM (
    SELECT CAST ('<M>' + REPLACE(@Input, '|', '</M><M>') + '</M>' AS XML) AS Data
) AS A 
CROSS APPLY Data.nodes ('/M') AS Split(a);

Достоинства обновлений и альтернативные способы решения

Переход на новые версии

Переход на SQL Server 2016 открывает доступ к функции STRING_SPLIT, которая значительно упрощает процесс разделения строк.

Альтернативы вне SQL Server

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

Тест производительности

Сравнительное тестирование поможет определить, какой из методов разделения строки наиболее эффективен.

Полезные ссылки

1. STRING_SPLIT (Transact-SQL) – SQL Server | Microsoft Learn
2. SQL Server – Как разделить значение, разделенное запятыми, на колонки – Stack Overflow
3. SQL Server – Сравнение функции STRING_SPLIT и XML метода
4. SQL Server – T-SQL: Противоположность конкатенации строк – как разделить строку на несколько записей – Stack Overflow