Stringa divisa T-SQL

Ho una colonna di SQL Server 2008 R2 contenente una stringa che ho bisogno di dividere con una virgola. Ho visto molte risposte su StackOverflow ma nessuno di loro funziona in R2. Mi sono assicurato di avere permessi di selezione su qualsiasi esempio di funzione di divisione. Qualsiasi aiuto molto apprezzato.

Ho usato questo SQL prima del quale potrebbe funzionare per voi: –

 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)[email protected]) END INSERT INTO @returnList SELECT @stringToSplit RETURN END 

e usarlo: –

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

Invece di CTE ricorsive e loop while, qualcuno ha considerato un approccio più basato su set?

 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 ); 

Maggiori informazioni sulle funzioni divise, perché (e la prova di ciò) mentre loop e CTE ricorsivi non si ridimensionano, e alternative migliori, se dividono le stringhe che provengono dal livello dell'applicazione:

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 l’attesa è finita in SQL Server 2016 hanno introdotto la funzione di stringa divisa: STRING_SPLIT

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

Tutti gli altri metodi per dividere string come XML, Tally table, while loop, ecc. Sono stati spazzati via da questa funzione STRING_SPLIT .

Ecco un articolo eccellente con il confronto delle prestazioni: sorprese e presupposti delle prestazioni: STRING_SPLIT

Il modo più semplice per farlo è utilizzare il formato XML .

1. Conversione di stringa in righe senza tabella

QUERY

 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) 

RISULTATO

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

2. Conversione in righe da una tabella con ID per ciascuna riga CSV

TABELLA DELLE SORGENTI

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

QUERY

 -- 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) 

RISULTATO

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

Avevo bisogno di un modo rapido per sbarazzarsi del +4 da un codice postale .

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

No proc … no UDF … solo un piccolo comando inline che fa quello che deve. Non elegante, non elegante.

Cambia il delimitatore secondo necessità, ecc. E funzionerà per qualsiasi cosa.

se si sostituisce

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

con

 WHILE LEN(@stringToSplit) > 0 

puoi eliminare l’ultimo inserto dopo il ciclo 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)[email protected]) END RETURN END 

Tutte le funzioni per la divisione delle stringhe che utilizzano un tipo di Looping (iterazioni) hanno prestazioni non buone. Dovrebbero essere sostituiti con una soluzione basata su set.

Questo codice è eccellente.

 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 

Ho dovuto scrivere qualcosa di simile di recente. Ecco la soluzione che ho trovato. È generalizzato per qualsiasi stringa di delimitazione e penso che sarebbe leggermente migliore:

 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 

Una soluzione che usa un CTE, se qualcuno dovesse aver bisogno di quello (a parte me, chi ovviamente lo ha fatto, è per questo che l’ho scritto).

 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 

C’è una versione corretta qui ma ho pensato che sarebbe bello aggiungere un po ‘di tolleranza ai guasti nel caso in cui avessero una virgola finale così come renderla in modo da poterla usare non come una funzione ma come parte di un pezzo di codice più grande . Nel caso in cui lo stai usando solo una volta e non hai bisogno di una funzione. Questo è anche per i numeri interi (che è quello per cui avevo bisogno), quindi potresti dover cambiare i tuoi tipi di dati.

 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)[email protected]) END --Add Last Value IF (LEN(LTRIM(RTRIM(@StringToSeperate)))>0) BEGIN INSERT INTO #IDs SELECT SUBSTRING(@StringToSeperate, 1, @Position) END SELECT * FROM #IDs 

Ho modificato leggermente la funzione di Andy Robinson. Ora puoi selezionare solo la parte richiesta dalla tabella di restituzione:

 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 @[email protected]+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)[email protected]) END SELECT @[email protected]+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

qui c’è una versione che può essere divisa su un pattern usando patindex, un semplice adattamento del post sopra. Avevo un caso in cui avevo bisogno di dividere una stringa che conteneva più caratteri separatori.

 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)[email protected]) END INSERT INTO @returnList SELECT @stringToSplit RETURN END select * from dbo.splitstring('stringa/stringb/x,y,z','%[/,]%'); 

il risultato è simile a questo

stringa stringb x y z

Personnaly uso questa funzione:

 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 ) 

Ho sviluppato un doppio splitter (prende due caratteri divisi) come richiesto qui . Potrebbe essere di qualche valore in questo thread vedendone il più referenziato per le query relative alla divisione delle stringhe.

 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 = @[email protected] 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 = [email protected], @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===','&','=') 

Possibile utilizzo (Ottieni il secondo valore di ogni divisione):

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

L’approccio spesso utilizzato con elementi XML si interrompe in caso di caratteri vietati. Questo è un approccio per utilizzare questo metodo con qualsiasi tipo di carattere, anche con il punto e virgola come delimitatore.

Il trucco è, in primo luogo, utilizzare SELECT SomeString AS [*] FOR XML PATH('') per far SELECT SomeString AS [*] FOR XML PATH('') tutti i caratteri vietati SELECT SomeString AS [*] FOR XML PATH('') correttamente. Questo è il motivo, perché sostituisco il delimitatore con un valore magico per evitare problemi con ; come delimitatore.

 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) 

Il risultato

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

Se hai bisogno di una soluzione ad-hoc rapida per casi comuni con un codice minimo di questo ricorsivo CTE due-liner lo 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 

Utilizzalo come una dichiarazione autonoma o aggiungi semplicemente le CTE sopra a qualsiasi query e sarai in grado di unire la tabella risultante b con altre per l’utilizzo in qualsiasi altra espressione.

edit (di Shnugo)

Se aggiungi un contatore, otterrai un indice di posizione insieme alla 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; 

Il risultato

 ns 1 1 2 2333 3 344 4 4 

Questo è più strettamente su misura. Quando faccio questo di solito ho un elenco delimitato da virgole di ID univoci (INT o BIGINT), che voglio cast come tabella da usare come join interno a un’altra tabella che ha una chiave primaria di INT o BIGINT. Voglio restituire una funzione con valori di tabella in linea in modo da avere il join più efficiente ansible.

L’utilizzo del campione sarebbe:

  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 

Ho rubato l’idea da http://sqlrecords.blogspot.com/2012/11/converting-delimited-list-to-table.html , cambiandola per essere valutata in-line e convertita in 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)[email protected]) END RETURN END 

Puoi usare questa funzione:

  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 

Ecco un esempio che è ansible utilizzare come funzione oppure è ansible inserire la stessa logica nella procedura. –SELEZIONA * da [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; 

/ *

Risposta a stringa split T-SQL
Basato sulle risposte di Andy Robinson e AviG
Miglioramento della funzionalità ref: funzione LEN non compresi gli spazi finali in SQL Server
Questo ‘file’ dovrebbe essere valido sia come file markdown che come file 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 

/ *

“ `

Casi di test: vedi l’URL referenziato come “funzionalità avanzata” sopra

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 

* /

Una soluzione basata su cso ricorsivo

 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; 

Con tutto il dovuto rispetto per @AviG questa è la versione bug gratuita della funzione da lui concepita per restituire tutti i token in pieno.

 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)[email protected]) END RETURN END 

La via più facile:

  1. Installa SQL Server 2016
  2. Utilizza STRING_SPLIT https://msdn.microsoft.com/en-us/library/mt684588.aspx

Funziona anche in Express Edition :).