Разбиваем строку на числа в T-SQL: функция Split

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

SQL Server 2016 и более новые версии: используйте STRING_SPLIT для разделения строк так же легко, как шеф-повар нарезает овощи.

-- Фрукты для салата? Или может быть это таблица с фруктами? Суть ясна.
SELECT value FROM STRING_SPLIT('apple,orange,banana', ',')

Если вы взаимодействуете с более ранними версиями, можете использовать мощь XML:

-- Дерево познания? Нет, это новые фрукты.
DECLARE @input VARCHAR(100) = 'apple,orange,banana'
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 для разделения строк

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

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

Заказные разделители? Без проблем!

Функция REPLACE позволяет вам заменить любой указанный разделитель на стандартный для последующего легкого разделения строки с использованием преобразования XML.

-- Когда разделители '|custom|' считают, что они уникальны и неповторимы.
DECLARE @input VARCHAR(MAX) = 'word1|custom|delimiter|word3'
SELECT Split.a.value('.', 'VARCHAR(MAX)') AS Value
FROM (SELECT CAST ('<X>' + REPLACE(@input, '|custom|', '</X><X>') + '</X>' AS XML) AS Data) AS A 
CROSS APPLY Data.nodes ('/X') AS Split(a)

Работа с большими объемами данных

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

CTE: код прекрасен, а жизнь — прекрасна

CTE облегчает процесс и делает код разделения понятным и удобным для поддержки. Используйте его для элегантной работы с рекурсией и сложными строковыми операциями.

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

Представьте, что эквивалент функции Split в T-SQL – это шеф-повар, нарезающий буханку хлеба:

"SELECT * FROM STRING_SPLIT('loaf,of,bread', ',')"

Как будто перед вами буханка хлеба (🍞), готовая к разрезанию. И вы хотите получить равномерно разделенные куски (🍽️).

До: [🍞]
На части: ',' 
После:  [🍽️ 'loaf' | 🍽️ 'of' | 🍽️ 'bread']

Каждая запятая превратилась в лезвие хлеборезки (🔪), отделяющее строки на индивидуальные элементы.

Увлекательное путешествие по методам победы над драконами

.NET для аварийной помощи (SQL Server 2008)

Если вы хотите расширить возможности, приготовьтесь к использованию кода .NET. Несмотря на сложности интеграции CLR, это дает больше производительности для запутанных операций и работы с объемами данных.

STRING_SPLIT и TRY_CAST: безопасность прежде всего!

STRING_SPLIT – позволит вам быстро и просто разделить строки, начиная с SQL Server 2016. Чтобы сохранить тип данных, используйте TRY_CAST.

-- Не забудьте про защиту! Только числа, пожалуйста.
SELECT TRY_CAST(value AS INT) AS Number
FROM STRING_SPLIT('10, ABC, 20', ',')
WHERE TRY_CAST(value AS INT) IS NOT NULL

Когда порядок — важен

STRING_SPLIT не гарантирует сохранение порядка. Чтобы сохранить порядок элементов, используйте свойство IDENTITY при вставке данных в таблицу:

-- Когда порядок — критичен для вашего начальника
DECLARE @TempTable TABLE (ID INT IDENTITY(1,1), value VARCHAR(255))

INSERT INTO @TempTable (value)
SELECT value FROM STRING_SPLIT('one,two,three', ',')

SELECT ID, value FROM @TempTable

Совет от эксперта

Аарон Бертранд, признанный эксперт в области SQL, предлагает свое мнение о сравнении различных методов разделения строк, что поможет вам выбрать наиболее оптимальный, экономящий время и серверные ресурсы.

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

1. STRING_SPLIT (Transact-SQL) – SQL Server | Microsoft Learn — детальное руководство по STRING_SPLIT.
2. sql server – T-SQL split string – Stack Overflow — обширный источник решений и обсуждений на тему разделения строк.
3. Использование INSERT OUTPUT в транзакциях SQL Server — простое руководство по пониманию транзакций и операций SQL.
4. Автоматическое заполнение ячеек в Excel — продвинутые методы, которые помогут вам с новыми идеями по рабыплению строк.