STRING_SPLIT (Transact-SQL)
Область применения:
SQL Server 2016 (13.x) и более поздних версий База данных SQL Azure Управляемый экземпляр SQL Azure Azure Synapse Analytics
Функция с табличным значением, которая разбивает строку на строки подстрок в зависимости от указанного знака разделения.
Уровень совместимости 130
STRING_SPLIT требует уровня совместимости не ниже 130. При уровне меньше 130 SQL Server не может найти функцию STRING_SPLIT.
Сведения об изменении уровня совместимости базы данных см. в статье Просмотр или изменение уровня совместимости базы данных.
Примечание
Конфигурация совместимости для STRING_SPLIT в Azure Synapse Analytics не требуется.
Синтаксические обозначения в 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 |