Программирование

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

Содержание:

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

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

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

CREATE TABLE student
(
ID INT ИДЕНТИФИКАТОР (1, 1),
имя_студента VARCHAR(MAX)
)

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

INSERT INTO student (student_name)
VALUES ('Monroy,Montanez,Marolahakis,Negley,Albright,Garofolo,Pereira,Johnson,Wagner,Conrad')
Создание таблицы и вставка данных

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

select * from student
Проверьте, были ли вставлены данные в таблицу “student”

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

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

Более того “STRING_SPLIT” вводит строку с разделенными подстроками и вводит один символ для использования в качестве разделителя или разделителя. Функция выводит одноколоночную таблицу, строки которой содержат подстроки. Имя выходного столбца “Значение”. Эта функция получает два параметра. Первый параметр — строка, второй — символ-разделитель или разделитель, на основании которого мы должны разделить строку. На выходе получается одноколоночная таблица, в которой присутствуют подстроки. Этот выходной столбец имеет имя «Значение» как показано на рисунке ниже. Более того, функция «РАЗДЕЛЕНИЕ СТРОКИ” функция table_valued возвращает пустую таблицу, если входная строка является NULL.

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

Каждый база данных это подключено с совместимость уровень. Это позволяет функция база данных’ поведение с быть совместимый сe конкретный SQL Сервер версия это работает на.

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

Ошибка возникает, если уровень совместимости базы данных ниже 130 “Invalid object name split_string”

Таким образом, нам необходимо установить уровень совместимости базы данных на 130 или выше. Чтобы установить уровень совместимости базы данных, выполним следующие шаги.

  • Сначала установите для базы данных режим «single_user_access_mode», используя следующий код.
ALTER DATABASE <[имя_базы_данных]> SET SINGLE_USER
  • Во-вторых, измените уровень совместимости базы данных, используя следующий код.
ALTER DATABASE <[имя_базы_данных]> SET COMPATIBILITY_LEVEL = 130
  • Верните базу данных в режим многопользовательского доступа с помощью следующего кода.
ALTER DATABASE <[имя_базы_данных]> SET MULTI_USER
USE [master]
GO
ALTER DATABASE [bridge_centrality] SET SINGLE_USER
ALTER DATABASE [bridge_centrality] SET COMPATIBILITY_LEVEL = 130
ALTER DATABASE [bridge_centrality] SET MULTI_USER
GO

Вывод будет следующим:

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

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

DECLARE @string_value VARCHAR(MAX) ;
SET @string_value='Monroy,Montanez,Marolahakis,Negley,Albright,Garofolo,Pereira,Johnson,Wagner,Conrad'
SELECT * FROM STRING_SPLIT (@string_value, ',')

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

Вывод из “split_string” функции build_in

Метод 2: Чтобы разделить строку, создайте пользовательскую таблично-значную функцию

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

CREATE FUNCTION [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

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

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 AS XML,
@string_value AS VARCHAR(2000),
@delimiter_value AS VARCHAR(15)
SET @string_value=(SELECT имя_студента FROM студент)
SET @delimiter_value =','
SET @xml_value = Cast(( '<имя студента>'
+ Replace(@string_value, @delimiter_value, '</studentname><имя студента>')
+ '</studentname>' ) AS XML)
SELECT @xml_value

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

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

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

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

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

DECLARE @xml_value AS XML,
@string_value AS VARCHAR(2000),
@delimiter_value AS VARCHAR(15)
SET @string_value=(SELECT имя_студента FROM студент)
SET @delimiter_value =','
SET @xml_value = Cast(( '<имя студента>'
+ Replace(@string_value, @delimiter_value, '</studentname><имя студента>')
+ '</studentname>' ) AS XML)
SELECT x.m.запрос('.').value('.', 'VARCHAR(15)') КАК ЗНАЧЕНИЕ
FROM @xml_value.nodes('/studentname') AS x(m)

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

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

Добавить комментарий