STRING_SPLIT (Transact-SQL)

Применимо к: SQL Server 2016 (13.x) и более поздние версии:конечная точка аналитики SQL Azure Synapse Analytics в хранилище MicrosoftFabric в базе данных SQL 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 должен быть константным значением, а не столбцом или переменной. Он также должен быть битом или типом В противном случае функция вызывает ошибку.

Выходные строки могут быть расположены в любом порядке. Порядок не гарантирует соответствие порядка подстроек во входной строке. Можно переопределить окончательный 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`

Примечание.

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

В. Объединение по значениям

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

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;