STRING_SPLIT (Transact-SQL)

  • Статья

Область применения: SQL Server 2016 (13.x) и более поздних версий База данных SQL Azure Управляемый экземпляр SQL Azure Конечная точка аналитики Synapse Analytics 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
О 3
D 2
E 1

Связанный контент