Divisão de texto ou blobs em linhas de dados e tabelas no SQL Server
Texto para linhas
Seguindo alguns dos meus outros artigos sobre funções SQL, esta é uma função que usamos como parte do código para pesquisar nossa biblioteca de documentos CMS.
Em primeiro lugar, a função declara uma tabela que será retornada posteriormente.
Em seguida, removemos quaisquer delimitadores duplicados para reduzir o volume de registros retornados.
Em seguida, percorremos a string e inserimos a palavra na tabela declarada a cada ocorrência do delimitador que declaramos no início.
Essa função usa associação de esquema, para que possa ser usada em outras funções associadas a esquema, que podem até mesmo ser associadas a uma tabela.
No SQL Server 2016, a função embutida STRING_SPLIT foi introduzida e, contanto que você tenha compatibilidade acima da versão 130, você pode usá-la para um único delimitador.
Conforme o trabalho progrediu, ultrapassamos string_split e agora podemos lidar com qualificadores de texto, mesmo quando presentes apenas em certas colunas.
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','"')
Use com PIVOT
Você também pode usar isso aplicado a si mesmo para dividir vários delimitadores, ou com um PIVOT, para criar uma tabela a partir de seu conjunto de resultados. Abaixo está uma função delimitada dupla, dividida novamente em uma tabela.
Usando isso, você pode diminuir rapidamente a quantidade de caracteres enviados entre computadores. Se necessário, você pode usar um delimitador de até 10 caracteres de comprimento.
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 |