Fractionnement de texte ou d'objets blob en lignes de données et tables dans SQL Server

Une fonction SQL Server pour diviser une liste de chaînes de texte délimitée par des caractères en lignes de données. Utilisé comme base pour un certain nombre d'autres fonctions

Texte en lignes

Suite à certains de mes autres articles sur les fonctions SQL, c'est une fonction que nous utilisons dans le cadre du code de recherche de notre bibliothèque de documents CMS.

Tout d'abord, la fonction déclare une table qui sera retournée plus tard.

Ensuite, nous supprimons tous les délimiteurs dupliqués pour réduire le volume d'enregistrements renvoyés.

Ensuite, nous bouclons la chaîne et insérons le mot dans la table déclarée à chaque occurrence du délimiteur que nous déclarons au début.

Cette fonction utilise la liaison de schéma, de sorte qu'elle puisse être utilisée dans d'autres fonctions liées au schéma, qui peuvent même être liées à une table.

Dans SQL Server 2016, la fonction intégrée STRING_SPLIT a été introduite, et tant que vous avez une compatibilité supérieure à la version 130, vous pouvez l'utiliser pour un seul délimiteur.

Au fur et à mesure de l'avancement des travaux, nous avons dépassé string_split et pouvons désormais gérer les qualificatifs de texte, même lorsqu'ils ne sont présents que sur certaines colonnes.

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','"')

Utiliser avec PIVOT

Vous pouvez également l'utiliser appliqué à lui-même pour diviser plusieurs délimiteurs, ou avec un PIVOT, pour créer une table à partir de votre jeu de résultats. Vous trouverez ci-dessous une fonction double délimitée, divisée en une table.

Grâce à cela, vous pouvez rapidement réduire la quantité de caractères envoyés entre les ordinateurs. Si nécessaire, vous pouvez utiliser un délimiteur de 10 caractères maximum.

Plus: SQL Server utilisant l'exemple 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