Dzielenie tekstu lub obiektów blob na wiersze danych i tabele w programie SQL Server
Tekst do wierszy
Kontynuując niektóre z moich innych artykułów na temat funkcji SQL, jest to funkcja, której używamy jako część kodu do przeszukiwania naszej biblioteki dokumentów CMS.
Przede wszystkim funkcja deklaruje tabelę, która zostanie zwrócona później.
Następnie usuwamy wszelkie zduplikowane separatory, aby zmniejszyć liczbę zwracanych rekordów.
Następnie przechodzimy przez ciąg i wstawiamy słowo do zadeklarowanej tabeli przy każdym wystąpieniu ogranicznika, który deklarujemy na początku.
Ta funkcja używa powiązania schematu, dzięki czemu może być używana w innych funkcjach powiązanych ze schematem, które można nawet powiązać z tabelą.
W SQL Server 2016 wprowadzono wbudowaną funkcję STRING_SPLIT i jeśli masz zgodność powyżej wersji 130, możesz jej użyć dla pojedynczego separatora.
W miarę postępu prac przekroczyliśmy string_split i teraz możemy obsługiwać kwalifikatory tekstu, nawet jeśli występują tylko w niektórych kolumnach.
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','"')Używaj z PIVOT
Możesz również użyć tego zastosowanego do siebie, aby podzielić wiele separatorów lub za pomocą PIVOT, aby utworzyć tabelę z zestawu wyników. Poniżej znajduje się funkcja z podwójnymi separatorami, podzielona z powrotem na tabelę.
Dzięki temu możesz szybko zmniejszyć liczbę znaków przesyłanych między komputerami. W razie potrzeby możesz użyć separatora o długości do 10 znaków.
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 |
