Функция SQL Server STRING_SPLIT

В этом руководстве вы узнаете, как использовать функцию SQL Server STRING_SPLIT() для разбиения строки на строку подстрок на основе указанного разделителя.

Введение в функцию SQL Server STRING_SPLIT()

Функция STRING_SPLIT() — это табличная функция, которая разбивает строку на таблицу, состоящую из строк подстрок на основе указанного разделителя.

Ниже показан синтаксис функции STRING_SPLIT():

STRING_SPLIT( input_string , separator )  

В этом синтаксисе:

• input_string — это символьное выражение, результатом которого является строка NVARCHAR, VARCHAR, NCHAR или CHAR.
• разделитель — одиночный символ, используемый в качестве разделителя для разделения.

Функция STRING_SPLIT() возвращает таблицу с одним столбцом, имя столбца которой — value. Эта результирующая таблица содержит строки, которые являются подстроками.

Обратите внимание, что значения в столбце значений могут быть в любом порядке сортировки. Чтобы получить ожидаемый порядок, вам нужно добавить предложение ORDER BY в оператор SELECT:

ORDER BY value [ASC|DESC]

Примеры функции SQL Server STRING_SPLIT()

Давайте рассмотрим несколько примеров использования функции STRING_SPLIT().

A) Использование функции STRING_SPLIT() для разделения строки значений, разделенных запятыми

В этом примере функция STRING_SPLIT() используется для анализа списка значений, разделенных запятыми:

SELECT 
    value  
FROM 
    STRING_SPLIT('red,green,,blue', ',');

Вот что получилось:

value
---------------
red
green
blue
(4 rows affected)

Третья строка пуста, поскольку входная строка содержит две последовательные запятые(,,). Чтобы получить непустые подстроки, добавьте предложение WHERE к оператору SELECT, как показано в следующем запросе:

SELECT 
    value  
FROM 
    STRING_SPLIT('red,green,,blue', ',')
WHERE
    TRIM(value)  '';

Вывод следующий:

value
---------------
red
green
blue
(3 rows affected)

Б) Использование функции STRING_SPLIT() для разделения строки, разделенной запятыми, в столбце

Иногда таблицы базы данных не нормализуются. Типичным примером этого является случай, когда столбец может хранить несколько значений, разделенных запятой(,).

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

Давайте создадим пример таблицы для демонстрации.

Сначала создайте новую таблицу с именем sales.contacts, в которой будет храниться контактная информация:

CREATE TABLE sales.contacts(
    id INT PRIMARY KEY IDENTITY,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    phones VARCHAR(500)
);

Во-вторых, вставьте несколько контактов в таблицу sales.contacts:

INSERT INTO 
    sales.contacts(first_name, last_name, phones)
VALUES
   ('John','Doe','(408)-123-3456,(408)-123-3457'),
   ('Jane','Doe','(408)-987-4321,(408)-987-4322,(408)-987-4323');

В-третьих, используйте функцию STRING_SPLIT() для разделения телефонных номеров и CROSS APPLY для объединения с таблицей sales.contacts:

SELECT 
    first_name, 
    last_name,
    value phone
FROM 
    sales.contacts
    CROSS APPLY STRING_SPLIT(phones, ',');

Вот что получилось:

C) Использование функции STRING_SPLIT() с агрегатной функцией

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

SELECT 
    CONCAT_WS(' ',first_name,last_name) full_name,
    COUNT(value) number_of_phones
FROM 
    sales.contacts
    CROSS APPLY STRING_SPLIT(phones, ',')
GROUP BY 
    CONCAT_WS(' ',first_name,last_name);

Вывод выглядит следующим образом:

В этом уроке вы узнали, как использовать функцию STRING_SPLIT() для разбиения строк на таблицу подстрок по указанному разделителю.