STRING_SPLIT (Transact-SQL)
Область применения: 
SQL Server 2016 (13.x) и более поздних версий База данных SQL Azure Управляемый экземпляр SQL Azure конечную точку аналитики SQL Azure Synapse Analytics в хранилище Microsoft Fabric в Microsoft Fabric
STRING_SPLIT — это табличное значение функция, которая разбивает строку на строки подстроек на основе указанного символа разделителя.
Уровень совместимости 130
STRING_SPLIT требует, чтобы уровень совместимости был не менее 130. Если уровень меньше 130, ядро СУБД не удается найти функциюSTRING_SPLIT.
Сведения об изменении уровня совместимости базы данных см. в статье Просмотр или изменение уровня совместимости базы данных.
Примечание.
Конфигурация совместимости не нужна STRING_SPLIT в Azure Synapse Analytics.
Соглашения о синтаксисе Transact-SQL
Синтаксис
STRING_SPLIT ( string , separator [ , enable_ordinal ] )
Аргументы
string
Выражение любого типа символа (например, nvarchar, varchar, nchar или char).
separator
Одно символьное выражение любого типа символа (например, nvarchar(1), varchar(1), nchar(1)или char(1)), которое используется в качестве разделителя для объединенных подстроек.
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 |
| C | 3 |
| D | 2 |
| E | 1 |