Rozdělení textu nebo objektů BLOB na datové řádky a tabulky na serveru SQL Server
Text do řádků
V návaznosti na některé z mých dalších článků o funkcích SQL se jedná o funkci, kterou používáme jako součást kódu pro prohledávání naší knihovny dokumentů CMS.
Nejprve funkce deklaruje tabulku, která se vrátí později.
Poté odstraníme duplicitní oddělovače, abychom snížili objem vrácených záznamů.
Potom procházíme řetězcem a vložíme slovo do deklarované tabulky při každém výskytu oddělovače, který deklarujeme na začátku.
Tato funkce používá vazbu schématu, takže ji lze použít v jiných funkcích vázaných na schéma, které lze dokonce svázat s tabulkou.
V SQL Server 2016 byla zavedena vestavěná funkce STRING_SPLIT a pokud máte kompatibilitu nad verzí 130, můžete ji použít pro jeden oddělovač.
Jak práce pokročila, překonali jsme string_split a nyní dokážeme zpracovat textové kvalifikátory, i když jsou k dispozici pouze v určitých sloupcích.
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
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','"')Použijte s PIVOT
Můžete také použít toto aplikované na sebe k rozdělení více oddělovačů nebo pomocí PIVOT k vytvoření tabulky z vaší sady výsledků. Níže je funkce s dvojitým oddělením, rozdělená zpět do tabulky.
Tímto způsobem můžete rychle snížit počet znaků odesílaných mezi počítači. V případě potřeby můžete použít oddělovač o délce až 10 znaků.
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])) PivDouble 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 |
