Cadeia de divisão de T-SQL

Eu tenho uma coluna do SQL Server 2008 R2 contendo uma seqüência de caracteres que eu preciso dividir por uma vírgula. Já vi muitas respostas no StackOverflow, mas nenhuma delas funciona no R2. Eu me certifiquei de selecionar permissions em qualquer exemplo de function de divisão. Qualquer ajuda muito apreciada.

Eu usei este SQL antes que pode funcionar para você: –

 CREATE FUNCTION dbo.splitstring ( @stringToSplit VARCHAR(MAX) ) RETURNS @returnList TABLE ([Name] [nvarchar] (500)) AS BEGIN DECLARE @name NVARCHAR(255) DECLARE @pos INT WHILE CHARINDEX(',', @stringToSplit) > 0 BEGIN SELECT @pos = CHARINDEX(',', @stringToSplit) SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1) INSERT INTO @returnList SELECT @name SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos) END INSERT INTO @returnList SELECT @stringToSplit RETURN END 

e usá-lo: –

 SELECT * FROM dbo.splitstring('91,12,65,78,56,789') 

Em vez de CTEs recursivos e loops while, alguém considerou uma abordagem mais baseada em conjuntos?

 CREATE FUNCTION [dbo].[SplitString] ( @List NVARCHAR(MAX), @Delim VARCHAR(255) ) RETURNS TABLE AS RETURN ( SELECT [Value] FROM ( SELECT [Value] = LTRIM(RTRIM(SUBSTRING(@List, [Number], CHARINDEX(@Delim, @List + @Delim, [Number]) - [Number]))) FROM (SELECT Number = ROW_NUMBER() OVER (ORDER BY name) FROM sys.all_objects) AS x WHERE Number < = LEN(@List) AND SUBSTRING(@Delim + @List, [Number], LEN(@Delim)) = @Delim ) AS y ); 

Mais sobre funções divididas, porque (e prova que) loops while e CTEs recursivos não escalam, e melhores alternativas, se dividindo strings vindo da camada de aplicação:

http://www.sqlperformance.com/2012/07/t-sql-queries/split-strings

http://www.sqlperformance.com/2012/08/t-sql-queries/splitting-strings-now-with-less-t-sql

https://sqlblog.org/2010/07/07/splitting-a-list-of-integers-another-roundup

Finalmente, a espera acabou no SQL Server 2016 eles introduziram function de seqüência de divisão: STRING_SPLIT

 select * From STRING_SPLIT ('a,b', ',') cs 

Todos os outros methods para dividir uma string como XML, Tally table, while loop, etc .. foram surpreendidos por esta function STRING_SPLIT .

Aqui está um excelente artigo com comparação de desempenho: surpresas e suposições de desempenho: STRING_SPLIT

A maneira mais fácil de fazer isso é usando o formato XML .

1. Conversão de string em linhas sem tabela

INQUERIR

 DECLARE @String varchar(100) = 'String1,String2,String3' -- To change ',' to any other delimeter, just change ',' to your desired one DECLARE @Delimiter CHAR = ',' SELECT LTRIM(RTRIM(Split.a.value('.', 'VARCHAR(100)'))) 'Value' FROM ( SELECT CAST ('' + REPLACE(@String, @Delimiter, '') + '' AS XML) AS Data ) AS A CROSS APPLY Data.nodes ('/M') AS Split(a) 

RESULTADO

  x---------x | Value | x---------x | String1 | | String2 | | String3 | x---------x 

2. Convertendo para linhas de uma tabela que possui um ID para cada linha CSV

TABELA FONTE

  x-----x--------------------------x | Id | Value | x-----x--------------------------x | 1 | String1,String2,String3 | | 2 | String4,String5,String6 | x-----x--------------------------x 

INQUERIR

 -- To change ',' to any other delimeter, just change ',' before '' to your desired one DECLARE @Delimiter CHAR = ',' SELECT ID,LTRIM(RTRIM(Split.a.value('.', 'VARCHAR(100)'))) 'Value' FROM ( SELECT ID,CAST ('' + REPLACE(VALUE, @Delimiter, '') + '' AS XML) AS Data FROM TABLENAME ) AS A CROSS APPLY Data.nodes ('/M') AS Split(a) 

RESULTADO

  x-----x----------x | Id | Value | x-----x----------x | 1 | String1 | | 1 | String2 | | 1 | String3 | | 2 | String4 | | 2 | String5 | | 2 | String6 | x-----x----------x 

Eu precisava de uma maneira rápida de me livrar do +4 de um código postal .

 UPDATE #Emails SET ZIPCode = SUBSTRING(ZIPCode, 1, (CHARINDEX('-', ZIPCODE)-1)) WHERE ZIPCode LIKE '%-%' 

Não proc … não UDF … apenas um pequeno comando inline que faz o que deve. Não é chique, não é elegante.

Altere o delimitador conforme necessário, etc, e ele funcionará para qualquer coisa.

se você replace

 WHILE CHARINDEX(',', @stringToSplit) > 0 

com

 WHILE LEN(@stringToSplit) > 0 

você pode eliminar essa última inserção após o loop while!

 CREATE FUNCTION dbo.splitstring ( @stringToSplit VARCHAR(MAX) ) RETURNS @returnList TABLE ([Name] [nvarchar] (500)) AS BEGIN DECLARE @name NVARCHAR(255) DECLARE @pos INT WHILE LEN(@stringToSplit) > 0 BEGIN SELECT @pos = CHARINDEX(',', @stringToSplit) if @pos = 0 SELECT @pos = LEN(@stringToSplit) SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1) INSERT INTO @returnList SELECT @name SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos) END RETURN END 

Todas as funções para divisão de cadeias de caracteres que usam algum tipo de Looping (iterações) têm um desempenho ruim. Eles devem ser substituídos por uma solução baseada em conjuntos.

Este código executa excelente.

 CREATE FUNCTION dbo.SplitStrings ( @List NVARCHAR(MAX), @Delimiter NVARCHAR(255) ) RETURNS TABLE WITH SCHEMABINDING AS RETURN ( SELECT Item = yivalue('(./text())[1]', 'nvarchar(4000)') FROM ( SELECT x = CONVERT(XML, '' + REPLACE(@List, @Delimiter, '') + '').query('.') ) AS a CROSS APPLY x.nodes('i') AS y(i) ); GO 

Eu tive que escrever algo assim recentemente. Aqui está a solução que eu criei. É generalizado para qualquer string delimitadora e acho que seria um pouco melhor:

 CREATE FUNCTION [dbo].[SplitString] ( @string nvarchar(4000) , @delim nvarchar(100) ) RETURNS @result TABLE ( [Value] nvarchar(4000) NOT NULL , [Index] int NOT NULL ) AS BEGIN DECLARE @str nvarchar(4000) , @pos int , @prv int = 1 SELECT @pos = CHARINDEX(@delim, @string) WHILE @pos > 0 BEGIN SELECT @str = SUBSTRING(@string, @prv, @pos - @prv) INSERT INTO @result SELECT @str, @prv SELECT @prv = @pos + LEN(@delim) , @pos = CHARINDEX(@delim, @string, @pos + 1) END INSERT INTO @result SELECT SUBSTRING(@string, @prv, 4000), @prv RETURN END 

Uma solução usando um CTE, se alguém precisar disso (além de mim, quem obviamente fez isso, é por isso que o escrevi).

 declare @StringToSplit varchar(100) = 'Test1,Test2,Test3'; declare @SplitChar varchar(10) = ','; with StringToSplit as ( select ltrim( rtrim( substring( @StringToSplit, 1, charindex( @SplitChar, @StringToSplit ) - 1 ) ) ) Head , substring( @StringToSplit, charindex( @SplitChar, @StringToSplit ) + 1, len( @StringToSplit ) ) Tail union all select ltrim( rtrim( substring( Tail, 1, charindex( @SplitChar, Tail ) - 1 ) ) ) Head , substring( Tail, charindex( @SplitChar, Tail ) + 1, len( Tail ) ) Tail from StringToSplit where charindex( @SplitChar, Tail ) > 0 union all select ltrim( rtrim( Tail ) ) Head , '' Tail from StringToSplit where charindex( @SplitChar, Tail ) = 0 and len( Tail ) > 0 ) select Head from StringToSplit 

Há uma versão correta aqui, mas eu achei que seria legal adicionar um pouco de tolerância a falhas, caso elas tivessem uma vírgula final, assim como fazer com que você pudesse usá-las não como uma function, mas como parte de um pedaço maior de código . Apenas no caso de você estar usando apenas uma vez e não precisar de uma function. Isso também é para inteiros (que é o que eu precisava) para que você possa ter que alterar seus tipos de dados.

 DECLARE @StringToSeperate VARCHAR(10) SET @StringToSeperate = '1,2,5' --SELECT @StringToSeperate IDs INTO #Test DROP TABLE #IDs CREATE TABLE #IDs (ID int) DECLARE @CommaSeperatedValue NVARCHAR(255) = '' DECLARE @Position INT = LEN(@StringToSeperate) --Add Each Value WHILE CHARINDEX(',', @StringToSeperate) > 0 BEGIN SELECT @Position = CHARINDEX(',', @StringToSeperate) SELECT @CommaSeperatedValue = SUBSTRING(@StringToSeperate, 1, @Position-1) INSERT INTO #IDs SELECT @CommaSeperatedValue SELECT @StringToSeperate = SUBSTRING(@StringToSeperate, @Position+1, LEN(@StringToSeperate)-@Position) END --Add Last Value IF (LEN(LTRIM(RTRIM(@StringToSeperate)))>0) BEGIN INSERT INTO #IDs SELECT SUBSTRING(@StringToSeperate, 1, @Position) END SELECT * FROM #IDs 

Eu modifiquei a function + Andy Robinson um pouco. Agora você pode selecionar apenas a peça necessária na tabela de retorno:

 CREATE FUNCTION dbo.splitstring ( @stringToSplit VARCHAR(MAX) ) RETURNS @returnList TABLE ([numOrder] [tinyint] , [Name] [nvarchar] (500)) AS BEGIN DECLARE @name NVARCHAR(255) DECLARE @pos INT DECLARE @orderNum INT SET @orderNum=0 WHILE CHARINDEX('.', @stringToSplit) > 0 BEGIN SELECT @orderNum=@orderNum+1; SELECT @pos = CHARINDEX('.', @stringToSplit) SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1) INSERT INTO @returnList SELECT @orderNum,@name SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos) END SELECT @orderNum=@orderNum+1; INSERT INTO @returnList SELECT @orderNum, @stringToSplit RETURN END Usage: 

SELECT Name FROM dbo.splitstring('ELIS.YD.CRP1.1.CBA.MDSP.T389.BT') WHERE numOrder=5

aqui está uma versão que pode dividir em um padrão usando patindex, uma adaptação simples do post acima. Eu tive um caso em que precisei dividir uma string que continha vários caracteres separadores.

 alter FUNCTION dbo.splitstring ( @stringToSplit VARCHAR(1000), @splitPattern varchar(10) ) RETURNS @returnList TABLE ([Name] [nvarchar] (500)) AS BEGIN DECLARE @name NVARCHAR(255) DECLARE @pos INT WHILE PATINDEX(@splitPattern, @stringToSplit) > 0 BEGIN SELECT @pos = PATINDEX(@splitPattern, @stringToSplit) SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1) INSERT INTO @returnList SELECT @name SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos) END INSERT INTO @returnList SELECT @stringToSplit RETURN END select * from dbo.splitstring('stringa/stringb/x,y,z','%[/,]%'); 

resultado parece com isso

stringa stringb x y z

Pessoal eu uso essa function:

 ALTER FUNCTION [dbo].[CUST_SplitString] ( @String NVARCHAR(4000), @Delimiter NCHAR(1) ) RETURNS TABLE AS RETURN ( WITH Split(stpos,endpos) AS( SELECT 0 AS stpos, CHARINDEX(@Delimiter,@String) AS endpos UNION ALL SELECT endpos+1, CHARINDEX(@Delimiter,@String,endpos+1) FROM Split WHERE endpos > 0 ) SELECT 'Id' = ROW_NUMBER() OVER (ORDER BY (SELECT 1)), 'Data' = SUBSTRING(@String,stpos,COALESCE(NULLIF(endpos,0),LEN(@String)+1)-stpos) FROM Split ) 

Eu desenvolvi um divisor duplo (toma dois caracteres divididos) conforme solicitado aqui . Poderia ser de algum valor neste segmento vendo o seu mais referenciado para consultas relacionadas à divisão de cadeias de caracteres.

 CREATE FUNCTION uft_DoubleSplitter ( -- Add the parameters for the function here @String VARCHAR(4000), @Splitter1 CHAR, @Splitter2 CHAR ) RETURNS @Result TABLE (Id INT,MId INT,SValue VARCHAR(4000)) AS BEGIN DECLARE @FResult TABLE(Id INT IDENTITY(1, 1), SValue VARCHAR(4000)) DECLARE @SResult TABLE(Id INT IDENTITY(1, 1), MId INT, SValue VARCHAR(4000)) SET @String = @String+@Splitter1 WHILE CHARINDEX(@Splitter1, @String) > 0 BEGIN DECLARE @WorkingString VARCHAR(4000) = NULL SET @WorkingString = SUBSTRING(@String, 1, CHARINDEX(@Splitter1, @String) - 1) --Print @workingString INSERT INTO @FResult SELECT CASE WHEN @WorkingString = '' THEN NULL ELSE @WorkingString END SET @String = SUBSTRING(@String, LEN(@WorkingString) + 2, LEN(@String)) END IF ISNULL(@Splitter2, '') != '' BEGIN DECLARE @OStartLoop INT DECLARE @OEndLoop INT SELECT @OStartLoop = MIN(Id), @OEndLoop = MAX(Id) FROM @FResult WHILE @OStartLoop < = @OEndLoop BEGIN DECLARE @iString VARCHAR(4000) DECLARE @iMId INT SELECT @iString = SValue+@Splitter2, @iMId = Id FROM @FResult WHERE Id = @OStartLoop WHILE CHARINDEX(@Splitter2, @iString) > 0 BEGIN DECLARE @iWorkingString VARCHAR(4000) = NULL SET @IWorkingString = SUBSTRING(@iString, 1, CHARINDEX(@Splitter2, @iString) - 1) INSERT INTO @SResult SELECT @iMId, CASE WHEN @iWorkingString = '' THEN NULL ELSE @iWorkingString END SET @iString = SUBSTRING(@iString, LEN(@iWorkingString) + 2, LEN(@iString)) END SET @OStartLoop = @OStartLoop + 1 END INSERT INTO @Result SELECT MId AS PrimarySplitID, ROW_NUMBER() OVER (PARTITION BY MId ORDER BY Mid, Id) AS SecondarySplitID , SValue FROM @SResult END ELSE BEGIN INSERT INTO @Result SELECT Id AS PrimarySplitID, NULL AS SecondarySplitID, SValue FROM @FResult END RETURN 

Uso:

 --FirstSplit SELECT * FROM uft_DoubleSplitter('ValueA=ValueB=ValueC=ValueD==ValueE&ValueA=ValueB=ValueC===ValueE&ValueA=ValueB==ValueD===','&',NULL) --Second Split SELECT * FROM uft_DoubleSplitter('ValueA=ValueB=ValueC=ValueD==ValueE&ValueA=ValueB=ValueC===ValueE&ValueA=ValueB==ValueD===','&','=') 

Uso possível (obtenha o segundo valor de cada divisão):

 SELECT fn.SValue FROM uft_DoubleSplitter('ValueA=ValueB=ValueC=ValueD==ValueE&ValueA=ValueB=ValueC===ValueE&ValueA=ValueB==ValueD===', '&', '=')AS fn WHERE fn.mid = 2 

A abordagem usada com frequência com elementos XML é interrompida em caso de caracteres proibidos. Esta é uma abordagem para usar este método com qualquer tipo de caractere, mesmo com o ponto-e-vírgula como delimitador.

O truque é, primeiro, usar SELECT SomeString AS [*] FOR XML PATH('') para obter todos os caracteres proibidos apropriadamente com escape. Essa é a razão, por que eu substituo o delimitador por um valor mágico para evitar problemas com ; como delimitador.

 DECLARE @Dummy TABLE (ID INT, SomeTextToSplit NVARCHAR(MAX)) INSERT INTO @Dummy VALUES (1,N'A&B;C;D;E, F') ,(2,N'"C" & ''D'';;D;E, F'); DECLARE @Delimiter NVARCHAR(10)=';'; --special effort needed (due to entities coding with "&code;")! WITH Casted AS ( SELECT * ,CAST(N'' + REPLACE((SELECT REPLACE(SomeTextToSplit,@Delimiter,N'§§Split$me$here§§') AS [*] FOR XML PATH('')),N'§§Split$me$here§§',N'') + N'' AS XML) AS SplitMe FROM @Dummy ) SELECT Casted.ID ,x.value(N'.',N'nvarchar(max)') AS Part FROM Casted CROSS APPLY SplitMe.nodes(N'/x') AS A(x) 

O resultado

 ID Part 1 A&B 1 C 1 D 1 E, F 2 "C" & 'D' 2  2 D 2 E, F 

Se você precisa de uma solução ad-hoc rápida para casos comuns com código mínimo que este recursivo CTE two-liner fará:

 DECLARE @s VARCHAR(200) = ',1,2,,3,,,4,,,,5,' ;WITH a AS (SELECT i=-1, j=0 UNION ALL SELECT j, CHARINDEX(',', @s, j + 1) FROM a WHERE j > i), b AS (SELECT SUBSTRING(@s, i+1, IIF(j>0, j, LEN(@s)+1)-i-1) s FROM a WHERE i >= 0) SELECT * FROM b 

Use isso como uma instrução autônoma ou apenas adicione os CTEs acima a qualquer uma de suas consultas e você poderá ingressar na tabela resultante b com outras pessoas para usar em outras expressões.

editar (por Shnugo)

Se você adicionar um contador, você obterá um índice de posição junto com a lista:

 DECLARE @s VARCHAR(200) = '1,2333,344,4' ;WITH a AS (SELECT n=0, i=-1, j=0 UNION ALL SELECT n+1, j, CHARINDEX(',', @s, j+1) FROM a WHERE j > i), b AS (SELECT n, SUBSTRING(@s, i+1, IIF(j>0, j, LEN(@s)+1)-i-1) s FROM a WHERE i >= 0) SELECT * FROM b; 

O resultado

 ns 1 1 2 2333 3 344 4 4 

Isso é mais específico. Quando faço isso, eu geralmente tenho uma lista delimitada por vírgulas de ids exclusivos (INT ou BIGINT), que eu quero converter como uma tabela para usar como uma junit interna em outra tabela que tenha uma chave primária de INT ou BIGINT. Eu quero uma function com valor de tabela in-line retornada para que eu tenha a junit mais eficiente possível.

O uso da amostra seria:

  DECLARE @IDs VARCHAR(1000); SET @IDs = ',99,206,124,8967,1,7,3,45234,2,889,987979,'; SELECT me.Value FROM dbo.MyEnum me INNER JOIN dbo.GetIntIdsTableFromDelimitedString(@IDs) ids ON me.PrimaryKey = ids.ID 

Eu roubei a ideia de http://sqlrecords.blogspot.com/2012/11/converting-delimited-list-to-table.html , alterando-a para ser avaliada na tabela em linha e convertida como INT.

 create function dbo.GetIntIDTableFromDelimitedString ( @IDs VARCHAR(1000) --this parameter must start and end with a comma, eg ',123,456,' --all items in list must be perfectly formatted or function will error ) RETURNS TABLE AS RETURN SELECT CAST(SUBSTRING(@IDs,Nums.number + 1,CHARINDEX(',',@IDs,(Nums.number+2)) - Nums.number - 1) AS INT) AS ID FROM [master].[dbo].[spt_values] Nums WHERE Nums.Type = 'P' AND Nums.number BETWEEN 1 AND DATALENGTH(@IDs) AND SUBSTRING(@IDs,Nums.number,1) = ',' AND CHARINDEX(',',@IDs,(Nums.number+1)) > Nums.number; GO 
 ALTER FUNCTION [dbo].func_split_string ( @input as varchar(max), @delimiter as varchar(10) = ";" ) RETURNS @result TABLE ( id smallint identity(1,1), csv_value varchar(max) not null ) AS BEGIN DECLARE @pos AS INT; DECLARE @string AS VARCHAR(MAX) = ''; WHILE LEN(@input) > 0 BEGIN SELECT @pos = CHARINDEX(@delimiter,@input); IF(@pos< =0) select @pos = len(@input) IF(@pos <> LEN(@input)) SELECT @string = SUBSTRING(@input, 1, @pos-1); ELSE SELECT @string = SUBSTRING(@input, 1, @pos); INSERT INTO @result SELECT @string SELECT @input = SUBSTRING(@input, @pos+len(@delimiter), LEN(@input)-@pos) END RETURN END 

Você pode usar esta function:

  CREATE FUNCTION SplitString ( @Input NVARCHAR(MAX), @Character CHAR(1) ) RETURNS @Output TABLE ( Item NVARCHAR(1000) ) AS BEGIN DECLARE @StartIndex INT, @EndIndex INT SET @StartIndex = 1 IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character BEGIN SET @Input = @Input + @Character END WHILE CHARINDEX(@Character, @Input) > 0 BEGIN SET @EndIndex = CHARINDEX(@Character, @Input) INSERT INTO @Output(Item) SELECT SUBSTRING(@Input, @StartIndex, @EndIndex - 1) SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input)) END RETURN END GO 

Aqui está um exemplo que você pode usar como function ou também pode colocar a mesma lógica no procedimento. –SELECT * de [dbo] .fn_SplitString;

 CREATE FUNCTION [dbo].[fn_SplitString] (@CSV VARCHAR(MAX), @Delimeter VARCHAR(100) = ',') RETURNS @retTable TABLE ( [value] VARCHAR(MAX) NULL )AS BEGIN DECLARE @vCSV VARCHAR (MAX) = @CSV, @vDelimeter VARCHAR (100) = @Delimeter; IF @vDelimeter = ';' BEGIN SET @vCSV = REPLACE(@vCSV, ';', '~!~#~'); SET @vDelimeter = REPLACE(@vDelimeter, ';', '~!~#~'); END; SET @vCSV = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@vCSV, '&', '&'), '< ', '<'), '>', '>'), '''', '''), '"', '"'); DECLARE @xml XML; SET @xml = '' + REPLACE(@vCSV, @vDelimeter, '') + ''; INSERT INTO @retTable SELECT xivalue('.', 'varchar(max)') AS COLUMNNAME FROM @xml.nodes('//i')AS x(i); RETURN; END; 

/ *

Resposta à string dividida T-SQL
Baseado em respostas de Andy Robinson e AviG
Ref funcionalidade aprimorada: function LEN não incluindo espaços à direita no SQL Server
Este ‘arquivo’ deve ser válido como um arquivo de marcação e um arquivo SQL

“ `

* /

 CREATE FUNCTION dbo.splitstring ( --CREATE OR ALTER @stringToSplit NVARCHAR(MAX) ) RETURNS @returnList TABLE ([Item] NVARCHAR (MAX)) AS BEGIN DECLARE @name NVARCHAR(MAX) DECLARE @pos BIGINT SET @stringToSplit = @stringToSplit + ',' -- this should allow entries that end with a `,` to have a blank value in that "column" WHILE ((LEN(@stringToSplit+'_') > 1)) BEGIN -- `+'_'` gets around LEN trimming terminal spaces. See URL referenced above SET @pos = COALESCE(NULLIF(CHARINDEX(',', @stringToSplit),0),LEN(@stringToSplit+'_')) -- COALESCE grabs first non-null value SET @name = SUBSTRING(@stringToSplit, 1, @pos-1) --MAX size of string of type nvarchar is 4000 SET @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, 4000) -- With SUBSTRING fn (MS web): "If start is greater than the number of characters in the value expression, a zero-length expression is returned." INSERT INTO @returnList SELECT @name --additional debugging parameters below can be added -- + ' pos:' + CAST(@pos as nvarchar) + ' remain:''' + @stringToSplit + '''(' + CAST(LEN(@stringToSplit+'_')-1 as nvarchar) + ')' END RETURN END GO 

/ *

“ `

Casos de teste: veja o URL referenciado como “funcionalidade aprimorada” acima

SELECT *,LEN(Item+'_')-1 'L' from splitstring('a,,b')

 Item | L --- | --- a | 1 | 0 b | 1 

SELECT *,LEN(Item+'_')-1 'L' from splitstring('a,,')

 Item | L --- | --- a | 1 | 0 | 0 

SELECT *,LEN(Item+'_')-1 'L' from splitstring('a,, ')

 Item | L --- | --- a | 1 | 0 | 1 

SELECT *,LEN(Item+'_')-1 'L' from splitstring('a,, c ')

 Item | L --- | --- a | 1 | 0 c | 3 

* /

Uma solução baseada em recursividade

 declare @T table (iden int identity, col1 varchar(100)); insert into @T(col1) values ('ROOT/South America/Lima/Test/Test2') , ('ROOT/South America/Peru/Test/Test2') , ('ROOT//South America/Venuzuala ') , ('RtT/South America / ') , ('ROOT/South Americas// '); declare @split char(1) = '/'; select @split as split; with cte as ( select t.iden, case when SUBSTRING(REVERSE(rtrim(t.col1)), 1, 1) = @split then LTRIM(RTRIM(t.col1)) else LTRIM(RTRIM(t.col1)) + @split end as col1, 0 as pos , 1 as cnt from @T t union all select t.iden, t.col1 , charindex(@split, t.col1, t.pos + 1), cnt + 1 from cte t where charindex(@split, t.col1, t.pos + 1) > 0 ) select t1.*, t2.pos, t2.cnt , ltrim(rtrim(SUBSTRING(t1.col1, t1.pos+1, t2.pos-t1.pos-1))) as bingo from cte t1 join cte t2 on t2.iden = t1.iden and t2.cnt = t1.cnt+1 and t2.pos > t1.pos order by t1.iden, t1.cnt; 

Com todo o respeito a @AviG, esta é a versão livre de erros da function devida por ele para retornar todos os tokens na íntegra.

 IF EXISTS (SELECT * FROM sys.objects WHERE type = 'TF' AND name = 'TF_SplitString') DROP FUNCTION [dbo].[TF_SplitString] GO -- ============================================= -- Author: AviG -- Amendments: Parameterize the delimeter and included the missing chars in last token - Gemunu Wickremasinghe -- Description: Tabel valued function that Breaks the delimeted string by given delimeter and returns a tabel having split results -- Usage -- select * from [dbo].[TF_SplitString]('token1,token2,,,,,,,,token969',',') -- 969 items should be returned -- select * from [dbo].[TF_SplitString]('4672978261,4672978255',',') -- 2 items should be returned -- ============================================= CREATE FUNCTION dbo.TF_SplitString ( @stringToSplit VARCHAR(MAX) , @delimeter char = ',' ) RETURNS @returnList TABLE ([Name] [nvarchar] (500)) AS BEGIN DECLARE @name NVARCHAR(255) DECLARE @pos INT WHILE LEN(@stringToSplit) > 0 BEGIN SELECT @pos = CHARINDEX(@delimeter, @stringToSplit) if @pos = 0 BEGIN SELECT @pos = LEN(@stringToSplit) SELECT @name = SUBSTRING(@stringToSplit, 1, @pos) END else BEGIN SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1) END INSERT INTO @returnList SELECT @name SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos) END RETURN END 

A maneira mais fácil:

  1. Instalar o SQL Server 2016
  2. Use STRING_SPLIT https://msdn.microsoft.com/pt-br/library/mt684588.aspx

Funciona mesmo em edição expressa :).