Função SQL Server que divide texto em linhas de dados

Função SQL Server para dividir uma lista delimitada por qualquer caractere nas linhas de dados

Na sequência de alguns dos meus outros artigos da função SQL, esta é uma função que usamos como parte do código para pesquisar nossa biblioteca de documentos do CMS.

Em primeiro lugar, a função declara uma tabela que será retornada mais tarde.

Em seguida, tiramos os delimitadores duplicados para reduzir o volume de registros retornados.

Em seguida, seguimos a cadeia e inserimos a palavra na tabela declarada em cada ocorrência do delimitador que declaramos no início

SQL Code

CREATE FUNCTION TextToRows(@Delim NVARCHAR(1),@Value NVARCHAR(MAX))--Delimeter and Search String
RETURNS @Table TABLE(WordInt BIGINT IDENTITY(1,1) PRIMARY KEY,WordStr NVARCHAR(MAX)) --Return Table
AS BEGIN
SET @Value=LTRIM(RTRIM(@Value))--Trim forward/trailing spaces
WHILE (CHARINDEX(@Delim+@Delim,@Value,1)<>0) BEGIN
SET @Value=REPLACE(@Value,@Delim+@Delim,@Delim)--Remove double delims (if required)...
END
DECLARE @CurPos BIGINT=0
DECLARE @NextPos BIGINT=CHARINDEX(@Delim,@Value,@CurPos+1)
WHILE @NextPos>0 BEGIN
INSERT INTO @Table(WordStr) SELECT SUBSTRING(@Value,@CurPos+1,(@NextPos-@CurPos)-1)--Add first word if exists
SET @CurPos=@NextPos
SET @NextPos=CHARINDEX(@Delim,@Value,@CurPos+1)
END
INSERT INTO @Table(WordStr) SELECT RIGHT(@Value,LEN(@Value)-@CurPos)--Add last word (or whole word)
RETURN
END
GO
SELECT * FROM dbo.TextToRows(' ',' My Text String')

Result

WordInt	WordStr
1
2	My
3	Text
4	String

Você também pode usar isso aplicado para si mesmo para dividir vários delimitadores, ou com um PIVOT, para criar uma tabela do seu conjunto de resultados. Abaixo está uma função dupla delimitada, dividida novamente em uma tabela.

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.WordStr
FROM dbo.TextToRows(';',@Str) ttr
OUTER APPLY dbo.TextToRows('.',ttr.WordStr) ttr2) ttrd
PIVOT (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