STRING_SPLIT (Transact-SQL)

Область применения:yesSQL Server 2016 (13.x) и более поздних версий YesБаза данных SQL Azure YesУправляемый экземпляр SQL Azure yesAzure Synapse Analytics

Функция с табличным значением, которая разбивает строку на строки подстрок в зависимости от указанного знака разделения.

Уровень совместимости 130

STRING_SPLIT требует уровня совместимости не ниже 130. При уровне меньше 130 SQL Server не может найти функцию STRING_SPLIT.

Сведения об изменении уровня совместимости базы данных см. в статье Просмотр или изменение уровня совместимости базы данных.

Примечание

Конфигурация совместимости для STRING_SPLIT в Azure Synapse Analytics не требуется.

Topic link iconСинтаксические обозначения в Transact-SQL

Синтаксис

STRING_SPLIT ( string , separator [ , enable_ordinal ] )  

Аргументы

строка

Выражение любого символьного типа (например, nvarchar, varchar, nchar или char).

separatorязательно, количество

Отдельное выражение любого символьного типа (например, nvarchar(1) , varchar(1) , nchar(1) или char(1) ), которое используется в качестве разделителя сцепленных подстрок.

enable_ordinal

Выражениеint или bit, которое служит флагом для включения или отключения выходного столбца ordinal. Значение 1 позволяет включить столбец ordinal. Если выражение enable_ordinal не указано, равно NULL или имеет значение 0, столбец ordinal будет отключен.

Примечание

В настоящее время аргумент enable_ordinal и выходной столбец ordinal поддерживаются в Базе данных SQL Azure, Управляемом экземпляре SQL Azure и Azure Synapse Analytics (только для бессерверного пула SQL). Начиная с предварительной версии SQL Server 2022 (16.x), этот аргумент и выходной столбец доступны в SQL Server.

Типы возвращаемых данных

Если выходной столбец 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 должен иметь константное значение, а не столбец или переменную. Он также должен иметь тип данных bit или 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 вернула следующую результирующую таблицу:

value
Lorem
ipsum
dolor
sit
amet.

В следующем примере демонстрируется включение столбца ordinal путем передачи 1 для необязательного третьего аргумента:

SELECT * FROM STRING_SPLIT('Lorem ipsum dolor sit amet.', ' ', 1);

Затем эта инструкция возвращает следующую результирующую таблицу:

value ordinal
Lorem 1
ipsum 2
dolor 3
sit 4
amet. 5

Примеры

A. Разделение строки значений с разделителями-запятыми

Следующая инструкция анализирует разделенный запятыми список значений и возвращает все непустые токены:

DECLARE @tags NVARCHAR(400) = 'clothing,road,,touring,bike'  
  
SELECT value  
FROM STRING_SPLIT(@tags, ',')  
WHERE RTRIM(value) <> '';

Функция STRING_SPLIT вернет пустую строку, если между разделителями ничего нет. Condition RTRIM(value) <> '' удаляет пустые токены.

Б. Разделение строки значений с разделителями-запятыми в столбце

Таблица Product содержит столбец с разделенным запятыми списком тегов, как показано в следующем примере:

ProductId имя; Теги
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, ',');  

Результирующий набор:

ProductId имя; value
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;  

Г. Поиск по значению тега

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

Поиск продуктов с одним тегом (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)) + ',%';  

Е. Поиск строк по порядковым значениям

Следующая инструкция позволяет найти все строки с четным значением индекса:

SELECT *
FROM STRING_SPLIT('Austin,Texas,Seattle,Washington,Denver,Colorado', ',', 1)
WHERE ordinal % 2 = 0;  

Приведенная выше инструкция возвращает следующую таблицу:

value ordinal
Техас 2
Вашингтон 4
Колорадо 6

Ж. Упорядочение строк по порядковым номерам

Следующая инструкция возвращает разделенные значения подстрок входной строки и их порядковые значения, упорядоченные столбцу ordinal:

SELECT * FROM STRING_SPLIT('E-D-C-B-A', '-', 1) ORDER BY ordinal DESC;  

Приведенная выше инструкция возвращает следующую таблицу:

value ordinal
Объект 5
B 4
C 3
D 2
E 1

Next Steps