Содержание:
- Уровень совместимости базы данных:
- Метод 2: Чтобы разделить строку, создайте пользовательскую таблично-значную функцию
- Метод 3: Используйте XQuery для разделения строкового значения и преобразования разделенной строки в XML
В этой статье мы рассмотрим несколько способов разделения разграниченного значения строки. Это может быть достигнуто с помощью нескольких методов, включая.
- Использование функции 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
Метод 1: Используйте функцию STRING_SPLIT для разделения строки
В SQL Server 2016, “STRING_SPLIT” введена функция, которую можно использовать с уровнем совместимости 130 и выше. Если вы используете SQL Server версии 2016 или выше, вы можете использовать эту встроенную функцию.
Более того “STRING_SPLIT” вводит строку с разделенными подстроками и вводит один символ для использования в качестве разделителя или разделителя. Функция выводит одноколоночную таблицу, строки которой содержат подстроки. Имя выходного столбца “Значение”. Эта функция получает два параметра. Первый параметр — строка, второй — символ-разделитель или разделитель, на основании которого мы должны разделить строку. На выходе получается одноколоночная таблица, в которой присутствуют подстроки. Этот выходной столбец имеет имя «Значение» как показано на рисунке ниже. Более того, функция «РАЗДЕЛЕНИЕ СТРОКИ” функция table_valued возвращает пустую таблицу, если входная строка является NULL.
Уровень совместимости базы данных:
Каждый база данных это подключено с a совместимость уровень. Это позволяет функция база данных’ поведение с быть совместимый сe конкретный SQL Сервер версия это работает на.
Теперь вызовем функцию “string_split” для разделения строки, разделенной запятыми. Но уровень совместимости был меньше 130, поэтому возникла следующая ошибка. “Недопустимое имя объекта ‘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
Вывод будет следующим:
Теперь запустите этот код, чтобы получить требуемый результат.
DECLARE @string_value VARCHAR(MAX) ; SET @string_value='Monroy,Montanez,Marolahakis,Negley,Albright,Garofolo,Pereira,Johnson,Wagner,Conrad' SELECT * FROM STRING_SPLIT (@string_value, ',')
Вывод для этого запроса будет следующим:
Метод 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)
Набор результатов будет выглядеть следующим образом.
Метод 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
Вывод для этого запроса будет следующим:
Если вы хотите просмотреть весь 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)
Выходные данные будут выглядеть следующим образом: