Aufteilen von Text oder Blobs in Datenzeilen und Tabellen in SQL Server

Eine SQL Server-Funktion zum Aufteilen einer durch beliebige Zeichen begrenzten Textzeichenfolgenliste in Datenzeilen. Wird als Basis für eine Reihe anderer Funktionen und zum Ausführen von Blobs in Tabellen verwendet

Text zu Zeilen

In Anlehnung an einige meiner anderen Artikel zu SQL-Funktionen verwenden wir diese Funktion als Teil des Codes zum Durchsuchen unserer CMS-Dokumentbibliothek.

Zunächst deklariert die Funktion eine Tabelle, die später zurückgegeben wird.

Anschließend entfernen wir alle doppelten Trennzeichen, um die Menge der zurückgegebenen Datensätze zu reduzieren.

Dann durchlaufen wir den String und fügen das Wort bei jedem Vorkommen des Trennzeichens, das wir am Anfang deklarieren, in die deklarierte Tabelle ein.

Diese Funktion verwendet die Schemabindung, sodass sie in anderen schemagebundenen Funktionen verwendet werden kann, die sogar an eine Tabelle gebunden werden können.

In SQL Server 2016 wurde die eingebaute Funktion STRING_SPLIT eingeführt, und solange Sie eine Kompatibilität über Version 130 haben, können Sie diese für ein einzelnes Trennzeichen verwenden.

Mit fortschreitender Arbeit haben wir string_split übertroffen und können jetzt mit Textkennzeichnern umgehen, selbst wenn sie nur in bestimmten Spalten vorhanden sind.

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

Verwendung mit PIVOT

Sie können dies auch auf sich selbst angewendet verwenden, um mehrere Trennzeichen aufzuteilen, oder mit einem PIVOT eine Tabelle aus Ihrer Ergebnismenge erstellen. Unten ist eine doppelt getrennte Funktion, die wieder in eine Tabelle aufgeteilt ist.

Auf diese Weise können Sie die Menge der zwischen Computern gesendeten Zeichen schnell verringern. Bei Bedarf können Sie ein Trennzeichen mit einer Länge von bis zu 10 Zeichen verwenden.

Mehr: SQL Server mit PIVOT-Beispiel

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