División de texto o blobs en filas y tablas de datos en SQL Server

Una función de SQL Server para dividir una lista de cadenas de texto delimitada por cualquier carácter en filas de datos. Se utiliza como base para otras funciones.

Texto a filas

Siguiendo con algunos de mis otros artículos sobre funciones SQL, esta es una función que usamos como parte del código para buscar en nuestra biblioteca de documentos CMS.

En primer lugar, la función declara una tabla que se devolverá más tarde.

Luego eliminamos los delimitadores duplicados para reducir el volumen de registros devueltos.

Luego recorremos la cadena e insertamos la palabra en la tabla declarada en cada aparición del delimitador que declaramos al principio.

Esta función utiliza el enlace de esquema, por lo que se puede utilizar en otras funciones enlazadas a esquema, que incluso se pueden enlazar a una tabla.

En SQL Server 2016, se introdujo la función incorporada STRING_SPLIT, y siempre que tenga compatibilidad por encima de la versión 130, puede usarla para un solo delimitador.

A medida que avanzaba el trabajo, hemos superado string_split y ahora podemos manejar calificadores de texto, incluso cuando solo están presentes en ciertas columnas.

SQL Code - Basic Function

CREATE FUNCTION [dbo].[TextToRows](@Delim NVARCHAR(10),@Value NVARCHAR(MAX))--Delimeter and Search StringRETURNS @Table TABLE(WordInt BIGINT IDENTITY(1,1) PRIMARY KEY,WordStr NVARCHAR(MAX)) WITH SCHEMABINDING --Return TableAS BEGINSET @Value=LTRIM(RTRIM(@Value))--Trim forward/trailing spacesWHILE (CHARINDEX(@Delim+@Delim,@Value,1)<>0) BEGINSET @Value=REPLACE(@Value,@Delim+@Delim,@Delim)--Remove double delims (if required)...ENDDECLARE @CurPos BIGINTSET @CurPos=0DECLARE @NextPos BIGINTSET @NextPos=CHARINDEX(@Delim,@Value,@CurPos+1)WHILE @NextPos>0 BEGININSERT INTO @Table(WordStr)SELECT REPLACE(SUBSTRING(@Value,@CurPos,(@NextPos-@CurPos)),@Delim,'')--Add first word if existsSET @CurPos=@NextPosSET @NextPos=CHARINDEX(@Delim,@Value,@CurPos+1)ENDINSERT INTO @Table(WordStr) SELECT REPLACE(SUBSTRING(@Value,@CurPos,LEN(@Value)),@Delim,'')--Add last word (or whole word)RETURNENDGOSELECT * FROM TextToRows(',','Gavin,Clayton,Test,Data')

Result

Gavin
Clayton
Test
Data

New SQL Code - With Text Qualifiers

CREATE FUNCTION dbo.[TextToRowsText](@Delim NVARCHAR(10),@Value NVARCHAR(MAX),@Text NVARCHAR(1))--Delimeter and Search StringRETURNS @Table TABLE(WordInt BIGINT IDENTITY(1,1) PRIMARY KEY,WordStr NVARCHAR(MAX)) --Return TableAS BEGINDECLARE @TextOn INT=(CASE WHEN LEFT(@Value,1)=@Text AND LEN(@Text)>0 THEN 1 ELSE 0 END)DECLARE @NextPos BIGINT=CHARINDEX((CASE WHEN @TextOn=1 THEN @Text+@Delim ELSE @Delim END),@Value,LEN(@Delim)+(@TextOn))WHILE @NextPos>0 BEGININSERT INTO @Table(WordStr) SELECT SUBSTRING(@Value,LEN(@Text)+@TextOn,(@NextPos-(LEN(@Text)+@TextOn)))SET @Value=SUBSTRING(@Value,@NextPos+@TextOn+LEN(@Delim),9999999)SET @TextOn=(CASE WHEN LEFT(@Value,1)=@Text AND LEN(@Text)>0 THEN 1 ELSE 0 END)SET @NextPos=CHARINDEX((CASE WHEN @TextOn=1 THEN @Text+@Delim ELSE @Delim END),@Value,LEN(@Text)+@TextOn)ENDSET @TextOn=(CASE WHEN LEFT(@Value,1)=@Text AND LEN(@Text)>0 THEN 1 ELSE 0 END)IF LEN(@Value)>0 INSERT INTO @Table(WordStr) SELECT SUBSTRING(@Value,LEN(@Text)+@TextOn,(CASE WHEN RIGHT(@Value,1)=@Text THEN LEN(@Value)-(LEN(@Text)+@TextOn) ELSE 9999999 END))RETURNENDGOSELECT * FROM dbo.TextToRowsText(',','Gavin,"Clayton","Test",Data','"')

Usar con PIVOT

También puede usar esto aplicado a sí mismo para dividir múltiples delimitadores, o con un PIVOT, para crear una tabla a partir de su conjunto de resultados. A continuación se muestra una función delimitada doble, dividida nuevamente en una tabla.

Con esto, puede disminuir rápidamente la cantidad de caracteres enviados entre computadoras. Si es necesario, puede utilizar un delimitador de hasta 10 caracteres de longitud.

Más: SQL Server usando el ejemplo de PIVOT

Double Delimited & Pivot

DECLARE @Str NVARCHAR(1000)='1;1.2;1.2.3;1.2.3.4'SELECT * FROM (SELECT ttr.WordStr Orig,ttr2.WordInt,ttr2.WordStrFROM dbo.TextToRows(';',@Str) ttrOUTER APPLY dbo.TextToRows('.',ttr.WordStr) ttr2) ttrdPIVOT (MAX(WordStr) FOR WordInt IN ([1],[2],[3],[4])) Piv

Double Delimited Pivot Result

Orig	1	2	3	4
1	1	NULL	NULL	NULL
1.2	1	2	NULL	NULL
1.2.3	1	2	3	NULL
1.2.3.4	1	2	3	4