Usando T-SQL, retorne o n-ésimo elemento delimitado de uma string

Eu preciso criar uma function que retornará o enésimo elemento de uma string delimitada.

Para um projeto de migration de dados, estou convertendo registros de auditoria JSON armazenados em um database do SQL Server em um relatório estruturado usando o script SQL. O objective é entregar um script sql e uma function sql usada pelo script sem nenhum código.

(Esta é uma correção de curto prazo será usada enquanto um novo recurso de auditoria é adicionado ao aplicativo ASP.NET/MVC)

Não há falta de string delimitada para exemplos de tabelas disponíveis. Eu escolhi um exemplo de Expressão de Tabela Comum http://www.sqlperformance.com/2012/07/t-sql-queries/split-strings

Exemplo: eu quero retornar 67 de ‘1,222,2,67,888,1111’

Esta é a resposta mais fácil para recuperar o 67 ( tipo seguro! ):

SELECT CAST('' + REPLACE('1,222,2,67,888,1111',',','') + '' AS XML).value('/x[4]','int') 

Esta questão não é sobre uma abordagem de divisão de cadeia , mas sobre como obter o enésimo elemento . A maneira mais fácil e totalmente inlineável seria este IMO:

Este é um verso real para obter a parte 2 delimitada por um espaço:

 DECLARE @input NVARCHAR(100)=N'part1 part2 part3'; SELECT CAST(N'' + REPLACE(@input,N' ',N'') + N'' AS XML).value('/x[2]','nvarchar(max)') 

Claro que você pode usar variables para delimitador e posição (use sql:column para recuperar a posição diretamente do valor de uma consulta):

 DECLARE @dlmt NVARCHAR(10)=N' '; DECLARE @pos INT = 2; SELECT CAST(N'' + REPLACE(@input,@dlmt,N'') + N'' AS XML).value('/x[sql:variable("@pos")][1]','nvarchar(max)') 

Se sua string pode include caracteres proibidos , você ainda pode fazer isso dessa maneira. Apenas use FOR XML PATH em sua string primeiro para replace todos os caracteres proibidos pela seqüência de escape de ajuste implicitamente.

É um caso muito especial se – adicionalmente – o seu delimitador for o ponto e vírgula . Nesse caso, substituo o delimitador primeiro por ‘# DLMT #’ e substituo isso pelas tags XML finalmente:

 SET @input=N'Some < , > and &;Other äöü@€;One more'; SET @dlmt=N';'; SELECT CAST(N'' + REPLACE((SELECT REPLACE(@input,@dlmt,'#DLMT#') AS [*] FOR XML PATH('')),N'#DLMT#',N'') + N'' AS XML).value('/x[sql:variable("@pos")][1]','nvarchar(max)'); 

Aqui está a minha solução inicial … É baseado no trabalho de Aaron Bertrand http://www.sqlperformance.com/2012/07/t-sql-queries/split-strings

Eu simplesmente mudei o tipo de retorno para torná-lo uma function escalar.

Exemplo: SELECT dbo.GetSplitString_CTE (‘1,222,2,67,888,1111’, ‘,’, 4)

 CREATE FUNCTION dbo.GetSplitString_CTE ( @List VARCHAR(MAX), @Delimiter VARCHAR(255), @ElementNumber int ) RETURNS VARCHAR(4000) AS BEGIN DECLARE @result varchar(4000) DECLARE @Items TABLE ( position int IDENTITY PRIMARY KEY, Item VARCHAR(4000) ) DECLARE @ll INT = LEN(@List) + 1, @ld INT = LEN(@Delimiter); WITH a AS ( SELECT [start] = 1, [end] = COALESCE(NULLIF(CHARINDEX(@Delimiter, @List, @ld), 0), @ll), [value] = SUBSTRING(@List, 1, COALESCE(NULLIF(CHARINDEX(@Delimiter, @List, @ld), 0), @ll) - 1) UNION ALL SELECT [start] = CONVERT(INT, [end]) + @ld, [end] = COALESCE(NULLIF(CHARINDEX(@Delimiter, @List, [end] + @ld), 0), @ll), [value] = SUBSTRING(@List, [end] + @ld, COALESCE(NULLIF(CHARINDEX(@Delimiter, @List, [end] + @ld), 0), @ll)-[end]-@ld) FROM a WHERE [end] < @ll ) INSERT @Items SELECT [value] FROM a WHERE LEN([value]) > 0 OPTION (MAXRECURSION 0); SELECT @result=Item FROM @Items WHERE position=@ElementNumber RETURN @result; END GO 

E se:

 CREATE FUNCTION dbo.NTH_ELEMENT (@Input NVARCHAR(MAX), @Delim CHAR = '-', @N INT = 0) RETURNS NVARCHAR(MAX) AS BEGIN RETURN (SELECT VALUE FROM STRING_SPLIT(@Input, @Delim) ORDER BY (SELECT NULL) OFFSET @N ROWS FETCH NEXT 1 ROW ONLY) END 

Em um raro momento de loucura, pensei que a divisão seria muito mais fácil se usássemos o XML para analisá-lo:

(Usando as variables ​​da resposta do @Gary Kindel)

 declare @xml xml set @xml = '' + replace(@list,@Delimiter,'') + '' select el = split.el.value('.','varchar(max)') from @xml.nodes('/split/el') split(el)) 

Isso lista todos os elementos da string, divididos pelo caractere especificado.

Podemos usar um teste xpath para filtrar valores vazios, e um teste xpath adicional para restringir isso ao elemento em que estamos interessados. Na function completa de Gary se torna:

 alter FUNCTION dbo.GetSplitString_CTE ( @List VARCHAR(MAX), @Delimiter VARCHAR(255), @ElementNumber int ) RETURNS VARCHAR(max) AS BEGIN declare @xml xml set @xml = '' + replace(@list,@Delimiter,'') + '' declare @ret varchar(max) set @ret = (select el = split.el.value('.','varchar(max)') from @xml.nodes('/split/el[string-length(.)>0][position() = sql:variable("@elementnumber")]') split(el)) return @ret END 

você pode colocar este select no UFN. se você precisar, pode personalizá-lo para especificar também o delimitador. Nesse caso, seu ufn terá duas inputs. número Nth e delimitador para usar.

  DECLARE @tlist varchar(max)='10,20,30,40,50,60,70,80,90,100' DECLARE @i INT=1, @nth INT=3 While len(@tlist) <> 0 BEGIN IF @i=@nth BEGIN select Case when charindex(',',@tlist) <> 0 Then LEFT(@tlist,charindex(',',@tlist)-1) Else @tlist END END Select @tlist = Case when charindex(',',@tlist) <> 0 Then substring(@tlist,charindex(',',@tlist)+1,len(@tlist)) Else '' END SELECT @i=@i+1 END 

Eu não posso comentar sobre a solução de Gary por causa da minha baixa reputação

Eu sei que Gary estava fazendo referência a outro link.

Eu tenho lutado para entender por que precisamos dessa variável

 @ld INT = LEN(@Delimiter) 

Eu também não entendo porque o charindex tem que começar na posição de comprimento do delimitador, @ld

Eu testei com muitos exemplos com um único caractere delimitador e eles funcionam. Na maioria das vezes, o caractere delimitador é um caractere único. No entanto, como o desenvolvedor incluiu o ld como comprimento do delimitador, o código precisa funcionar para delimitadores que tenham mais de um caractere

Nesse caso, o seguinte caso falhará

11 ,,, 22 ,,, 33 ,,, 44 ,,, 55 ,,,

Eu clonado a partir dos códigos deste link. http://codebetter.com/raymondlewallen/2005/10/26/quick-t-sql-to-parse-a-delimited-string/

Eu testei vários cenários, incluindo os delimitadores que têm mais de um caractere

 alter FUNCTION [dbo].[split1] ( @string1 VARCHAR(8000) -- List of delimited items , @Delimiter VARCHAR(40) = ',' -- delimiter that separates items , @ElementNumber int ) RETURNS varchar(8000) AS BEGIN declare @position int declare @piece varchar(8000)='' declare @returnVal varchar(8000)='' declare @Pattern varchar(50) = '%' + @Delimiter + '%' declare @counter int =0 declare @ld int = len(@Delimiter) declare @ls1 int = len (@string1) declare @foundit int = 0 if patindex(@Pattern , @string1) = 0 return '' if right(rtrim(@string1),1) <> @Delimiter set @string1 = @string1 + @Delimiter set @position = patindex(@Pattern , @string1) + @ld -1 while @position > 0 begin set @counter = @counter +1 set @ls1 = len (@string1) if (@ls1 >= @ld) set @piece = left(@string1, @position - @ld) else break if (@counter = @ElementNumber) begin set @foundit = 1 break end if len(@string1) > 0 begin set @string1 = stuff(@string1, 1, @position, '') set @position = patindex(@Pattern , @string1) + @ld -1 end else set @position = -1 end if @foundit =1 set @returnVal = @piece else set @returnVal = '' return @returnVal 

@a – o valor (fe ‘a / bb / ccc / dddd / ee / ff / ….’)

@p – a posição desejada (1,2,3 …)

@d – o delimitador (‘/’)

trim (substring (replace (@ a, @ d, replicar (”, len (@a))), (@ p-1) * len (@a) + 1, len (@a)))

O único problema é – se a peça desejada tiver espaços em branco à direita ou à esquerda, eles serão cortados.

Completamente Baseado no artigo de https://exceljet.net/formula/split-text-with-delimiter

Eu não tenho reputação suficiente para comentar, então estou adicionando uma resposta. Por favor ajuste conforme apropriado.

Eu tenho um problema com a resposta de Gary Kindel para casos em que não há nada entre os dois delimitadores

Se você selecionar * de dbo.GetSplitString_CTE (‘abc ^ def ^^ ghi’, ‘^’, 3) você obtém ghi em vez de uma string vazia

Se você comentar a linha WHERE LEN ([value])> 0, você obtém o resultado desejado