¿Cómo dividir una cadena por un carácter delimitado en SQL Server?

En este artículo, analizaremos varias formas de dividir el valor de cadena delimitada. Se puede lograr utilizando múltiples métodos, incluido.

• Uso de la función STRING_SPLIT para dividir la cadena
• Cree una función con valores de tabla definida por el usuario para dividir la cadena,
• Utilice XQuery para dividir el valor de la cadena y transformar una cadena delimitada en XML

En primer lugar, necesitamos crear una tabla e insertar en ella datos que se utilizarán en los tres métodos. La tabla debe contener una sola fila con la identificación del campo y una cadena con caracteres delimitadores. Cree una tabla llamada “estudiante” usando el siguiente código.

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

Inserte los nombres de los estudiantes separados por comas en una sola fila ejecutando el siguiente código.

INSERT INTO student (student_name)
VALUES ('Monroy,Montanez,Marolahakis,Negley,Albright,Garofolo,Pereira,Johnson,Wagner,Conrad')

Verifique si se han insertado datos en la tabla o no utilizando el siguiente código.

select * from student

Método 1: utilice la función STRING_SPLIT para dividir la cadena

En SQL Server 2016, “STRING_SPLIT” Se introdujo una función que se puede utilizar con el nivel de compatibilidad 130 y superior. Si utiliza la versión 2016 de SQL Server o superior, puede utilizar esta función integrada.

Además “STRING_SPLIT” ingresa una cadena que tiene subcadenas delimitadas e ingresa un carácter para usar como delimitador o separador. La función genera una tabla de una sola columna cuyas filas contienen las subcadenas. El nombre de la columna de salida es “Valor”. Esta función obtiene dos parámetros. El primer parámetro es una cadena y el segundo es un carácter delimitador o separador en función del cual tenemos que dividir la cadena. El resultado contiene una tabla de una sola columna en la que hay subcadenas. Esta columna de salida se llama “Valor” como podemos ver en la siguiente figura. Además, el “DIVISIÓN DE CUERDAS” La función table_valued devuelve una tabla vacía si la cadena de entrada es NULL.

Nivel de compatibilidad de la base de datos:

Cada base de datos es conectado con a compatibilidad nivel. Él permite el bases de datos comportamiento a ser compatible con elmi particular SQL Servidor versión él carreras en.

Ahora llamaremos a la función “string_split” para dividir la cadena delimitada por comas. Pero el nivel de compatibilidad era inferior a 130, por lo que se generó el siguiente error. “Nombre de objeto no válido ‘SPLIT_STRING'”

Por lo tanto, debemos establecer el nivel de compatibilidad de la base de datos en 130 o superior. Entonces seguiremos estos pasos para establecer el nivel de compatibilidad de la base de datos.

• En primer lugar, configure la base de datos en “single_user_access_mode” utilizando el siguiente código.

ALTER DATABASE <(database_name)> SET SINGLE_USER

• En segundo lugar, cambie el nivel de compatibilidad de la base de datos utilizando el siguiente código.

ALTER DATABASE <(database_name)> SET COMPATIBILITY_LEVEL = 130

• Vuelva a poner la base de datos en modo de acceso multiusuario utilizando el siguiente código.

ALTER DATABASE <(database_name)> 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

La salida será:

Ahora ejecute este código para obtener el resultado requerido.

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

El resultado de esta consulta será:

Método 2: Para dividir la cadena, cree una función con valores de tabla definida por el usuario

Ciertamente, este método tradicional es compatible con todas las versiones de SQL Server. En esta técnica crearemos una función definida por el usuario para dividir la cadena por carácter delimitado usando “SUBCADE” función, “CARÍNDEX”y bucle while. Esta función se puede utilizar para agregar datos a la tabla de salida ya que su tipo de retorno es “tabla”.

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

Ahora ejecute el siguiente script para llamar a una función de división para dividir la cadena por carácter delimitador.

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)

El conjunto de resultados será así.

Método 3: Utilice XQuery para dividir el valor de la cadena y transformar una cadena delimitada en XML

Como las funciones definidas por el usuario agotan los recursos, debemos evitar estas funciones. Otra opción es la función incorporada “string_split”, pero esta función se puede utilizar para bases de datos cuyo nivel de compatibilidad sea 130 o superior. Entonces aquí viene otra solución para resolver esta difícil tarea. Una cadena se puede dividir usando lo siguiente XML.

DECLARE @xml_value AS XML,
        @string_value AS VARCHAR(2000),
        @delimiter_value AS VARCHAR(15)
SET @string_value=(SELECT student_name FROM student)
SET @delimiter_value=","
SET @xml_value = Cast(( '<studentname>'
                  + Replace(@string_value, @delimiter_value, '</studentname><studentname>')
                  + '</studentname>' ) AS XML)
SELECT @xml_value

El resultado de esta consulta será:

Si desea ver el archivo XML completo. Clic en el enlace. Una vez que haya hecho clic, el código del enlace se verá así.

Ahora la cadena XML debería procesarse más. Finalmente, usaremos “x-Query” para consultar desde XML.

DECLARE @xml_value AS XML,
        @string_value AS VARCHAR(2000),
        @delimiter_value AS VARCHAR(15)
SET @string_value=(SELECT student_name FROM student)
SET @delimiter_value=","
SET @xml_value = Cast(( '<studentname>'
                  + Replace(@string_value, @delimiter_value, '</studentname><studentname>')
                  + '</studentname>' ) AS XML)
SELECT   x.m.query('.').value('.', 'VARCHAR(15)') AS VALUE
FROM   @xml_value.nodes('/studentname') AS x(m)

La salida será así: