Эта статья появилась на свет в тот момент, когда я работал над другим материалом, посвященным строковым функциям. До той поры мне не доводилось сталкиваться с новой функцией STRING_SPLIT (), реализованной в версии SQL Server 2016; я познакомился с ней, когда работал над темой, которую в будущем надеюсь разобрать в нескольких статьях. Этой функцией можно воспользоваться в случае, когда целевая база данных находится в режиме COMPATIBILITY_MODE = 130.
Поясню: STRING_SPLIT () — это функция, возвращающая табличное значение. Она предоставляет нам возможность анализировать строку с разделителями, передаваемую в качестве параметра (наряду с символом-разделителем, выступающим в роли второго параметра). Функция возвращает выходной столбец, то есть результирующий набор значений таблицы, предоставляющий строку для каждого отдельного значения, помещенного между символами-разделителями.
Синтаксис функции STRING_SPLIT
С точки зрения синтаксиса конструкция, обеспечивающая возвращение результатов выполнения функции STRING_SPLIT, напоминает произвольный вызов функции, возвращающей табличное значение (листинг 1).
В качестве входного строкового параметра может выступать любое из следующих значений:
- строковое значение с разделителями;
- присваивание переменной типа varchar ();
- табличный столбец при использовании оператора CROSS APPLY.
Примеры возвращаемых результатов выполнения функции STRING_SPLIT ()
Давайте посмотрим, каким образом каждый из перечисленных выше вариантов входного строкового параметра для функции STRING_SPLIT возвращает результаты.
Первый пример — это передача строкового значения. Вне всякого сомнения, это самый простой метод решения задачи. Пользователь должен представить только входную строку и символ разделителя. Следующий пример позволит вам более ясно представить картину. Посмотрим, что произойдет, когда мы в демонстрационных целях введем в функцию список аэропортов, посещенных мною на протяжении прошлого года (листинг 2). Результаты показаны на рисунке 1.
![]() |
Рисунок 1. Результаты выполнения листинга 2 |
Разумеется, эти результаты можно пропустить через фильтр с предикатами в предложении WHERE, чтобы отсортировать их с помощью предложения ORDER BY (листинг 3). Результаты приведены на рисунке 2.
![]() |
Рисунок 2. Результаты выполнения листинга 3 |
Второй пример — нормализация данных с помощью переменной для строки с разделителями и курсора. Ведь, кроме всего прочего, мы можем объявить переменную типа varchar (n) или varchar (max) и передать ее в качестве первого параметра; результаты будут аналогичными. В этом примере мы имеем дело с очисткой денормализованных данных из одной таблицы посредством парсинга и вставкой их в нормализованную таблицу. Я воспользуюсь тремя таблицами (листинг 4):
- [events] — таблица, где хранятся связанные с конференциями SQL Cruise данные, характеризующие предстоящие события до конца 2018 года;
- [event_ports_denormalized] — таблица, где хранится столбец с идентификаторами со ссылками на столбец идентификаторов в таблице [events] и столбец ports_list_csv, содержащий список всех портов для данного события с разделителями-запятыми;
- [event_ports] — таблица, которая будет представлять собой нормализованную версию таблицы [event_ports_denormalized].
Чтобы придать осмысленность полученным результатам, нам нужно заполнить эти таблицы (листинг 5).
Содержимое таблиц выглядит так, как показано на рисунке 3.
![]() |
Рисунок 3. Содержимое демонстрационных таблиц |
Если бы мы захотели взглянуть на результирующий набор портов и связанных с ними событий, то убедились бы, что результирующие данные слабо поддаются дальнейшей обработке и объединяются с другими таблицами в данном состоянии (листинг 6). Результаты показаны на рисунке 4.
![]() |
Рисунок 4. Результаты выполнения листинга 6 |
Итак, сейчас самое время преобразовать эти данные, чтобы их можно было использовать с другими таблицами в базе данных. Приступаем к нормализации.
Наш подход будет состоять в следующем. С помощью курсора мы будем назначать переменным каждое значение формата csv и ассоциированный с ним идентификатор, с тем чтобы далее с использованием функции STRING_SPLIT () проанализировать эти значения и ввести их в таблицу [event_ports] (листинг 7).
Теперь с помощью запроса, приведенного в листинге 8, мы можем просмотреть нормализованный листинг событий и связанных с ними портов. Результаты представлены на рисунке 5.
![]() |
Рисунок 5. Результаты запроса листинга 8 |
Третий пример — использование табличных значений на протяжении действия оператора CROSS APPLY (курсор в этом случае не применяется). Последний процесс я продемонстрировал вам для того, чтобы показать, насколько проще он выглядит при использовании оператора CROSS APPLY. Возьмем финальный этап использования функции STRING_SPLIT () с целью нормализации данных с разделителями, где напрямую применяется оператор CROSS APPLY, а курсор не используется вообще. Я очистил таблицу [event_ports] и теперь готов использовать процесс, показанный в листинге 9, для перезагрузки этой таблицы.
Результаты получаются те же самые, причем состоят они всего лишь из четырех строк кода (листинг 10). Результаты представлены на рисунке 6.
![]() |
Рисунок 6. Результаты запроса листинга 10 |
Значение NULL или пустая строка?
Последнее обстоятельство, которое мне хотелось бы рассмотреть: что происходит, когда мы сталкиваемся с двумя символами-разделителями, расположенными последовательно во входной строке функции. Возьмем для примера параметр (обращая внимание на стоящие друг за другом запятые между аэропортами Ketchikan и Juneau), приведенный в листинге 11.
Значение строки, возвращенной для стоящих друг за другом разделителей-запятых, воспринимается как пустая строка, но не как NULL (рисунок 7).
![]() |
Рисунок 7. Результаты запроса листинга 11 |
Возможно, это связано с тем, что код составлялся давно, а может быть, все дело в недостаточной квалификации разработчика, но так или иначе во многих приложениях и решениях, не использующих язык SQL, значения с разделителями-запятыми (CSV) часто хранятся в одном столбце или поле. Во многих случаях SQL Server подключается к работе с этими денормализованными данными либо в ходе миграции, при выполнении операций по извлечению, преобразованию и загрузке, либо при осуществлении координированного слияния системных данных, которое выполнялось бы более эффективно с помощью функции STRING_SPLIT (). В сущности, это функция синтаксического разбора, которая нормализует данные, не являющиеся нормализованными. В результате открываются новые возможности простой нормализации данных — внутренних в контексте базы данных SQL Server или получаемых в процессе взаимодействия с внешним источником данных.
SELECT value FROM STRING_SPLIT( input string varchar(max), separator character varchar(1));
SELECT value FROM STRING_SPLIT ('AZO,YYZ,SEA,PDX,SFO,RKV,AMS', ',');
SELECT value FROM STRING_SPLIT ('AZO,YYZ,SEA,PDX,SFO,RKV,AMS', ',') WHERE value LIKE 'A%' ORDER BY value;
CREATE TABLE [events] ( id INT IDENTITY(1,1), event_name VARCHAR(100) ); CREATE TABLE [event_ports_denormalized] ( id INT NOT NULL, ports_list_csv VARCHAR(500) NOT NULL ); CREATE TABLE [event_ports] ( id INT NOT NULL, port_name VARCHAR(50) NOT NULL );
INSERT INTO [events](event_name) VALUES ('SQL Cruise Alaska 2017') , ('SQL Cruise Caribbean 2018') , ('SQL Cruise Alaska 2018') INSERT INTO [event_ports_denormalized](id, ports_list_csv) VALUES (1,'Seattle,Ketchikan,Juneau,Skagway,Victoria') , (2,'Miami,St. Thomas,Tortola,Nassau') , (3,'Seattle,Ketchikan,Juneau,Skagway,Victoria');
SELECT E.event_name, EPD.ports_list_csv FROM [events] AS E INNER JOIN [event_ports_denormalized] AS EPD ON E.id = EPD.id ORDER BY E.id;
DECLARE @id INT DECLARE @csv VARCHAR(500) DECLARE splitting_cursor FOR SELECT id, ports_list_csv FROM [event_ports_denormalized]; OPEN splitting_cursor FETCH NEXT FROM splitting_cursor INTO @id, @csv WHILE @@FETCH_STATUS = 0 BEGIN INSERT INTO [event_ports] (id, port_name) SELECT @id, SS.value FROM STRING_SPLIT(@csv,',') AS SS FETCH NEXT FROM splitting_cursor INTO @id, @csv END CLOSE splitting_cursor; DEALLOCATE splitting_cursor;
SELECT E.event_name, EP.port_name FROM [events] AS E INNER JOIN [event_ports] AS EP ON E.id = EP.id ORDER BY E.id;
INSERT INTO [event_ports] (id, port_name) SELECT id, SS.value FROM [event_ports_denormalized] AS EPD CROSS APPLY STRING_SPLIT(EPD.ports_list_csv,',') AS SS;
SELECT E.event_name, EP.port_name FROM [events] AS E INNER JOIN [event_ports] AS EP ON E.id = EP.id ORDER BY E.id;
SELECT value FROM STRING_SPLIT('Ketchikan,,Juneau,Skagway,Victoria',',');