Как разбить строку на символ с разделителями в SQL Server?

В этой статье мы обсудим несколько способов разделения строкового значения с разделителями. Это может быть достигнуто с использованием нескольких методов, в том числе.

• Использование функции STRING_SPLIT для разделения строки
• Создайте пользовательскую функцию с табличным значением для разделения строки,
• Используйте XQuery для разделения строкового значения и преобразования строки с разделителями в XML.

Прежде всего, нам нужно создать таблицу и вставить в нее данные, которые будут использоваться во всех трех методах. Таблица должна содержать одну строку с идентификатором поля и строку с символами-разделителями. Создайте таблицу с именем «student», используя следующий код.

CREATE TABLE student (ID INT IDENTITY (1, 1), student_name VARCHAR(MAX))

Вставьте имена учащихся, разделенные запятыми, в одну строку, выполнив следующий код.

ВСТАВЬТЕ В ЦЕННОСТИ студента (имя_ученика) («Монрой, Монтаньес, Маролахакис, Негли, Олбрайт, Гарофоло, Перейра, Джонсон, Вагнер, Конрад»)Создание таблицы и вставка данных

Проверьте, были ли данные вставлены в таблицу или нет, используя следующий код.

выберите * от студентаПроверьте, были ли данные вставлены в таблицу «студент»

Способ 1: используйте функцию STRING_SPLIT для разделения строки

В SQL Server 2016 была представлена ​​функция «STRING_SPLIT», которую можно использовать с уровнем совместимости 130 и выше. Если вы используете версию SQL Server 2016 или более позднюю, вы можете использовать эту встроенную функцию.

Кроме того, «STRING_SPLIT» вводит строку с разделенными подстроками и вводит один символ для использования в качестве разделителя или разделителя. Функция выводит таблицу с одним столбцом, строки которой содержат подстроки. Имя выходного столбца «Значение». Эта функция получает два параметра. Первый параметр — это строка, а второй — символ-разделитель или разделитель, на основе которого мы должны разделить строку. Вывод содержит таблицу с одним столбцом, в которой присутствуют подстроки. Этот выходной столбец называется «Значение», как мы можем видеть на рисунке ниже. Кроме того, функция table_value «STRING SPLIT» возвращает пустую таблицу, если входная строка равна NULL.

Уровень совместимости базы данных:

Каждая база данных связана с уровнем совместимости. Он обеспечивает совместимость поведения базы данных с конкретной версией SQL Server, на которой она работает.

Теперь мы будем вызывать функцию «string_split», чтобы разделить строку, разделенную запятыми. Но уровень совместимости был меньше 130, поэтому возникла следующая ошибка. “Недопустимое имя объекта “SPLIT_STRING””

Ошибка возникает, если уровень совместимости базы данных ниже 130 «Недопустимое имя объекта split_string»

Таким образом, нам нужно установить уровень совместимости базы данных 130 или выше. Поэтому мы выполним этот шаг, чтобы установить уровень совместимости базы данных.

• Прежде всего установите базу данных в «single_user_access_mode», используя следующий код.

ИЗМЕНИТЬ БАЗУ ДАННЫХ <[database_name]> УСТАНОВИТЬ ОДНОГО_ПОЛЬЗОВАТЕЛЯ

• Во-вторых, измените уровень совместимости базы данных, используя следующий код.

ИЗМЕНИТЬ БАЗУ ДАННЫХ <[database_name]> УСТАНОВИТЬ СОВМЕСТИМОСТЬ_УРОВЕНЬ = 130

• Верните базу данных в режим многопользовательского доступа, используя следующий код.

ИЗМЕНИТЬ БАЗУ ДАННЫХ <[database_name]> УСТАНОВИТЬ МНОГОПОЛЬЗОВАТЕЛЬСКИЙ [master]
ПЕРЕЙТИ ИЗМЕНИТЬ БАЗУ ДАННЫХ [bridge_centrality] УСТАНОВИТЬ ОДНОГО ПОЛЬЗОВАТЕЛЯ ИЗМЕНИТЬ БАЗУ ДАННЫХ [bridge_centrality] УСТАНОВИТЬ COMPATIBILITY_LEVEL = 130 ИЗМЕНИТЬ БАЗУ ДАННЫХ [bridge_centrality] УСТАНОВИТЬ МНОГОПОЛЬЗОВАТЕЛЬСКИЙ GO

Вывод будет:

Измените уровень совместимости на 130

Теперь запустите этот код, чтобы получить требуемый результат.

DECLARE @string_value VARCHAR(MAX) ; SET @string_value=”Монрой,Монтанес,Маролахакис,Негли,Олбрайт,Гарофоло,Перейра,Джонсон,Вагнер,Конрад” SELECT * FROM STRING_SPLIT(@string_value, ‘,’)

Вывод для этого запроса будет:

Вывод из функции build_in «split_string»

Способ 2. Чтобы разделить строку, создайте определяемую пользователем функцию с табличным значением.

Безусловно, этот традиционный метод поддерживается всеми версиями SQL Server. В этом методе мы создадим определяемую пользователем функцию для разделения строки по символу-разделителю, используя функцию «SUBSTRING», «CHARINDEX» и цикл while. Эту функцию можно использовать для добавления данных в выходную таблицу, поскольку ее тип возвращаемого значения — «таблица».

СОЗДАТЬ ФУНКЦИЮ [dbo].[split_string]
( @string_value NVARCHAR(MAX), @delimiter_character CHAR(1) ) RETURNS @result_set TABLE(splited_data NVARCHAR(MAX)) BEGIN DECLARE @start_position INT, @ending_position INT SELECT @start_position = 1, @ending_position = CHARINDEX(@delimiter_character, @ string_value) WHILE @start_position < LEN(@string_value) + 1 BEGIN IF @ending_position = 0 SET @ending_position = LEN(@string_value) + 1 INSERT INTO @result_set (splited_data) VALUES(SUBSTRING(@string_value, @start_position, @ending_position - @start_position)) SET @start_position = @ending_position + 1 SET @ending_position = CHARINDEX(@delimiter_character, @string_value, @start_position) END RETURN END

Теперь выполните приведенный ниже скрипт, чтобы вызвать функцию разделения для разделения строки по символу-разделителю.

DECLARE @student_name VARCHAR(MAX); DECLARE @delimiter CHAR(1); SET @delimiter=”,” SET @student_name =(SELECT student_name FROM student) SELECT * FROM dbo.split_string(@student_name, @delimiter)

Результирующий набор будет таким.

Набор результатов из табличной функции «split_string»

Способ 3. Используйте XQuery для разделения строкового значения и преобразования строки с разделителями в XML.

Поскольку определяемые пользователем функции исчерпывают ресурсы, мы должны избегать этих функций. Другим вариантом является встроенная функция «string_split», но эту функцию можно использовать для базы данных, для которой уровень совместимости 130 или выше. Итак, вот еще одно решение для решения этой сложной задачи. Строка может быть разделена с помощью следующего XML.

DECLARE @xml_value КАК XML, @string_value КАК VARCHAR(2000), @delimiter_value КАК VARCHAR(15) SET @string_value=(ВЫБЕРИТЕ имя_ученика ИЗ ученика) ЗАДАТЬ @delimiter_value=”,” SET @xml_value = Cast(( ‘<имя_учащегося>‘ + Заменить(@string_value, @delimiter_value, ‘‘) + ‘‘ ) AS XML) SELECT @xml_value

Вывод для этого запроса будет:

Шаг 1 для разделения строки с помощью XML

Если вы хотите просмотреть весь файл XML. Нажмите на ссылку. После того, как вы нажмете, код ссылки будет выглядеть так.

XML-файл, содержащий отдельные узлы строки для разделения

Теперь XML-строка должна быть обработана дальше. Наконец, мы будем использовать «x-Query» для запроса из XML.

DECLARE @xml_value КАК XML, @string_value КАК VARCHAR(2000), @delimiter_value КАК VARCHAR(15) SET @string_value=(ВЫБЕРИТЕ имя_ученика ИЗ ученика) ЗАДАТЬ @delimiter_value=”,” SET @xml_value = Cast(( ‘<имя_учащегося>‘ + Заменить(@string_value, @delimiter_value, ‘‘) + ‘‘ ) AS XML) SELECT xmquery(‘.’).value(‘.’, ‘VARCHAR(15)’ ) КАК ЗНАЧЕНИЕ ИЗ @xml_value.nodes(‘/studentname’) КАК x(m)

Вывод будет таким:

Использование «XQuery» для запросов из XML