Разбиваем строку на числа в 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, предлагает свое мнение о сравнении различных методов разделения строк, что поможет вам выбрать наиболее оптимальный, экономящий время и серверные ресурсы.
Полезные материалы
- STRING_SPLIT (Transact-SQL) – SQL Server | Microsoft Learn — детальное руководство по
STRING_SPLIT
. - sql server – T-SQL split string – Stack Overflow — обширный источник решений и обсуждений на тему разделения строк.
- Использование INSERT OUTPUT в транзакциях SQL Server — простое руководство по пониманию транзакций и операций SQL.
- Автоматическое заполнение ячеек в Excel — продвинутые методы, которые помогут вам с новыми идеями по рабыплению строк.