在SQL Server中将文本或Blob拆分为数据行和表
SQL Server函数，用于将由任何字符分隔的文本字符串列表拆分为数据行。用作许多其他功能的基础
文字到行
在我其他一些SQL函数文章之后，我们将此函数用作搜索CMS文档库的代码的一部分。
首先，该函数声明一个表，稍后将返回该表。
然后，我们去除所有重复的定界符以减少返回的记录量。
然后，我们遍历字符串，并在每次在开始时声明的定界符出现时，将单词插入声明的表中。
该函数使用架构绑定，因此可以在其他架构绑定函数中使用，甚至可以将其绑定到表。
在SQL Server 2016中，引入了内置函数STRING_SPLIT，只要您具有版本130以上的兼容性，就可以将其用于单个定界符。
随着工作的进行，我们已经超过了string_split，并且即使在某些列上也可以处理文本限定符。
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
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','"')与PIVOT一起使用
您还可以将其应用于自身以分割多个定界符，或与PIVOT一起使用，以根据结果集创建表。下面是一个双定界函数，分为表。
使用此功能，可以迅速减少计算机之间发送的字符数。如果需要，您可以使用长度不超过10个字符的定界符。
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 |
