Come faccio a dividere una stringa in modo da poter accedere all’elemento x?

Utilizzando SQL Server, come faccio a dividere una stringa in modo da poter accedere all’elemento x?

Prendi una stringa “Ciao John Smith”. Come posso dividere la stringa per spazio e accedere all’elemento all’indice 1 che dovrebbe restituire “John”?

È ansible trovare la soluzione in SQL User Defined Function per analizzare una stringa delimitata utile (da The Code Project ).

Puoi usare questa semplice logica:

Declare @products varchar(200) = '1|20|3|343|44|6|8765' Declare @individual varchar(20) = null WHILE LEN(@products) > 0 BEGIN IF PATINDEX('%|%', @products) > 0 BEGIN SET @individual = SUBSTRING(@products, 0, PATINDEX('%|%', @products)) SELECT @individual SET @products = SUBSTRING(@products, LEN(@individual + '|') + 1, LEN(@products)) END ELSE BEGIN SET @individual = @products SET @products = NULL SELECT @individual END END 

Non credo che SQL Server abbia una funzione di suddivisione integrata, quindi diversa da una UDF, l’unica altra risposta che conosco è quella di dirottare la funzione PARSENAME:

 SELECT PARSENAME(REPLACE('Hello John Smith', ' ', '.'), 2) 

PARSENAME prende una stringa e la divide sul carattere periodo. Prende un numero come secondo argomento, e quel numero specifica quale segmento della stringa restituire (lavorando da dietro in avanti).

 SELECT PARSENAME(REPLACE('Hello John Smith', ' ', '.'), 3) --return Hello 

Il problema ovvio è quando la stringa contiene già un punto. Continuo a pensare che l’utilizzo di una UDF sia il modo migliore … altri suggerimenti?

Per prima cosa, crea una funzione (usando CTE, l’espressione di una tabella comune elimina la necessità di una tabella temporanea)

  create function dbo.SplitString ( @str nvarchar(4000), @separator char(1) ) returns table AS return ( with tokens(p, a, b) AS ( select 1, 1, charindex(@separator, @str) union all select p + 1, b + 1, charindex(@separator, @str, b + 1) from tokens where b > 0 ) select p-1 zeroBasedOccurance, substring( @str, a, case when b > 0 then ba ELSE 4000 end) AS s from tokens ) GO 

Quindi, utilizzalo come qualsiasi tabella (o modificalo per adattarlo al tuo stored proc esistente) in questo modo.

 select s from dbo.SplitString('Hello John Smith', ' ') where zeroBasedOccurance=1 

Aggiornare

La versione precedente avrebbe avuto esito negativo per la stringa di input più lunga di 4000 caratteri. Questa versione si prende cura della limitazione:

 create function dbo.SplitString ( @str nvarchar(max), @separator char(1) ) returns table AS return ( with tokens(p, a, b) AS ( select cast(1 as bigint), cast(1 as bigint), charindex(@separator, @str) union all select p + 1, b + 1, charindex(@separator, @str, b + 1) from tokens where b > 0 ) select p-1 ItemIndex, substring( @str, a, case when b > 0 then ba ELSE LEN(@str) end) AS s from tokens ); GO 

L’utilizzo rimane lo stesso.

La maggior parte delle soluzioni qui utilizzate si utilizza durante loop o CTE ricorsive. Un approccio basato sul set sarà superiore, lo prometto:

 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

http://sqlblog.com/blogs/aaron_bertrand/archive/2010/07/07/splitting-a-list-of-integers-another-roundup.aspx

Puoi sfruttare una tabella numerica per eseguire l’analisi delle stringhe.

Crea una tabella di numeri fisici:

  create table dbo.Numbers (N int primary key); insert into dbo.Numbers select top 1000 row_number() over(order by number) from master..spt_values go 

Crea una tabella di test con 1000000 righe

  create table #yak (i int identity(1,1) primary key, array varchar(50)) insert into #yak(array) select 'a,b,c' from dbo.Numbers n cross join dbo.Numbers nn go 

Crea la funzione

  create function [dbo].[ufn_ParseArray] ( @Input nvarchar(4000), @Delimiter char(1) = ',', @BaseIdent int ) returns table as return ( select row_number() over (order by n asc) + (@BaseIdent - 1) [i], substring(@Input, n, charindex(@Delimiter, @Input + @Delimiter, n) - n) s from dbo.Numbers where n <= convert(int, len(@Input)) and substring(@Delimiter + @Input, n, 1) = @Delimiter ) go 

Uso (restituisce 3mil righe in 40s sul mio laptop)

  select * from #yak cross apply dbo.ufn_ParseArray(array, ',', 1) 

pulire

  drop table dbo.Numbers; drop function [dbo].[ufn_ParseArray] 

Le prestazioni qui non sono sorprendenti, ma chiamare una funzione su un tavolo da un milione di righe non è l'idea migliore. Se si esegue una divisione di stringa su più righe, eviterei la funzione.

Ecco un UDF che lo farà. Restituirà una tabella dei valori delimitati, non ha provato tutti gli scenari su di esso ma il tuo esempio funziona bene.

 CREATE FUNCTION SplitString ( -- Add the parameters for the function here @myString varchar(500), @deliminator varchar(10) ) RETURNS @ReturnTable TABLE ( -- Add the column definitions for the TABLE variable here [id] [int] IDENTITY(1,1) NOT NULL, [part] [varchar](50) NULL ) AS BEGIN Declare @iSpaces int Declare @part varchar(50) --initialize spaces Select @iSpaces = charindex(@deliminator,@myString,0) While @iSpaces > 0 Begin Select @part = substring(@myString,0,charindex(@deliminator,@myString,0)) Insert Into @ReturnTable(part) Select @part Select @myString = substring(@mystring,charindex(@deliminator,@myString,0)+ len(@deliminator),len(@myString) - charindex(' ',@myString,0)) Select @iSpaces = charindex(@deliminator,@myString,0) end If len(@myString) > 0 Insert Into @ReturnTable Select @myString RETURN END GO 

Lo chiameresti in questo modo:

 Select * From SplitString('Hello John Smith',' ') 

Modifica: soluzione aggiornata per gestire i delimitatori con len> 1 come in:

 select * From SplitString('Hello**John**Smith','**') 

Nessun codice, ma leggi l’articolo definitivo su questo. Tutte le soluzioni in altre risposte sono versioni di quelle elencate in questo articolo: Array ed elenchi in SQL Server 2005 e versioni successive

Personalmente, ho usato una soluzione di tabella Numbers molto spesso perché si adatta a quello che devo fare …

Qui inserisco un modo semplice di soluzione

 CREATE FUNCTION [dbo].[split]( @delimited NVARCHAR(MAX), @delimiter NVARCHAR(100) ) RETURNS @t TABLE (id INT IDENTITY(1,1), val NVARCHAR(MAX)) AS BEGIN DECLARE @xml XML SET @xml = N'' + REPLACE(@delimited,@delimiter,'') + '' INSERT INTO @t(val) SELECT r.value('.','varchar(MAX)') as item FROM @xml.nodes('/t') as records(r) RETURN END 

Esegui la funzione in questo modo

  select * from dbo.split('Hello John Smith',' ') 

Questa domanda non riguarda un approccio split split , ma su come ottenere l’ennesimo elemento .

Tutte le risposte qui stanno facendo una specie di suddivisione delle stringhe usando ricorsione, CTE , più CHARINDEX , REVERSE e PATINDEX , inventando funzioni, chiamate per i metodi CLR, tabelle numeriche, CROSS APPLY … La maggior parte delle risposte copre molte righe di codice.

Ma – se vuoi davvero niente di più di un approccio per ottenere l’ennesimo elemento – questo può essere fatto come vero one-liner , senza UDF, nemmeno una sotto-selezione … E come ulteriore vantaggio: digita sicuro

Ottieni la parte 2 delimitata da uno spazio:

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

Naturalmente puoi usare le variabili per delimitatore e posizione (usa sql:column per recuperare la posizione direttamente dal valore di una query):

 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 la tua stringa potrebbe includere caratteri proibiti (specialmente uno tra &>< ), puoi comunque farlo in questo modo. È sufficiente utilizzare FOR XML PATH sulla stringa per sostituire tutti i caratteri vietati con la sequenza di escape appropriata in modo implicito.

È un caso molto speciale se - in aggiunta - il tuo delimitatore è il punto e virgola . In questo caso sostituisco il delimitatore prima con '# DLMT #' e infine sostituisco questo con i tag XML:

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

Secondo me voi ragazzi lo state rendendo troppo complicato. Basta creare un UDF CLR e farlo.

 using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; using System.Collections.Generic; public partial class UserDefinedFunctions { [SqlFunction] public static SqlString SearchString(string Search) { List SearchWords = new List(); foreach (string s in Search.Split(new char[] { ' ' })) { if (!s.ToLower().Equals("or") && !s.ToLower().Equals("and")) { SearchWords.Add(s); } } return new SqlString(string.Join(" OR ", SearchWords.ToArray())); } }; 

Che dire dell’uso di string e values() ?

 DECLARE @str varchar(max) SET @str = 'Hello John Smith' DECLARE @separator varchar(max) SET @separator = ' ' DECLARE @Splited TABLE(id int IDENTITY(1,1), item varchar(max)) SET @str = REPLACE(@str, @separator, '''),(''') SET @str = 'SELECT * FROM (VALUES(''' + @str + ''')) AS V(A)' INSERT INTO @Splited EXEC(@str) SELECT * FROM @Splited 

Risultato ottenuto.

 id item 1 Hello 2 John 3 Smith 

Questo modello funziona bene e puoi generalizzare

 Convert(xml,''+Replace(FIELD,'.','')+'').value('(/n[INDEX])','TYPE') ^^^^^ ^^^^^ ^^^^ 

note FIELD , INDEX e TYPE .

Lascia una tabella con identificatori come

 sys.message.1234.warning.A45 sys.message.1235.error.O98 .... 

Quindi, puoi scrivere

 SELECT Source = q.value('(/n[1])', 'varchar(10)'), RecordType = q.value('(/n[2])', 'varchar(20)'), RecordNumber = q.value('(/n[3])', 'int'), Status = q.value('(/n[4])', 'varchar(5)') FROM ( SELECT q = Convert(xml,''+Replace(fieldName,'.','')+'') FROM some_TABLE ) Q 

spaccare e colare tutte le parti.

Io uso la risposta di frederic ma questo non ha funzionato in SQL Server 2005

L’ho modificato e sto usando select with union all e funziona

 DECLARE @str varchar(max) SET @str = 'Hello John Smith how are you' DECLARE @separator varchar(max) SET @separator = ' ' DECLARE @Splited table(id int IDENTITY(1,1), item varchar(max)) SET @str = REPLACE(@str, @separator, ''' UNION ALL SELECT ''') SET @str = ' SELECT ''' + @str + ''' ' INSERT INTO @Splited EXEC(@str) SELECT * FROM @Splited 

E il set di risultati è:

 id item 1 Hello 2 John 3 Smith 4 how 5 are 6 you 

Stavo cercando la soluzione su rete e il sotto funziona per me. Ref .

E tu chiami la funzione in questo modo:

 SELECT * FROM dbo.split('ram shyam hari gopal',' ') 

 SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION [dbo].[Split](@String VARCHAR(8000), @Delimiter CHAR(1)) RETURNS @temptable TABLE (items VARCHAR(8000)) AS BEGIN DECLARE @idx INT DECLARE @slice VARCHAR(8000) SELECT @idx = 1 IF len(@String)<1 OR @String IS NULL RETURN WHILE @idx!= 0 BEGIN SET @idx = charindex(@Delimiter,@String) IF @idx!=0 SET @slice = LEFT(@String,@idx - 1) ELSE SET @slice = @String IF(len(@slice)>0) INSERT INTO @temptable(Items) VALUES(@slice) SET @String = RIGHT(@String,len(@String) - @idx) IF len(@String) = 0 break END RETURN END 

Ancora un altro non ottiene una parte della stringa dalla funzione delimitatore:

 create function GetStringPartByDelimeter ( @value as nvarchar(max), @delimeter as nvarchar(max), @position as int ) returns NVARCHAR(MAX) AS BEGIN declare @startPos as int declare @endPos as int set @endPos = -1 while (@position > 0 and @endPos != 0) begin set @startPos = @endPos + 1 set @endPos = charindex(@delimeter, @value, @startPos) if(@position = 1) begin if(@endPos = 0) set @endPos = len(@value) + 1 return substring(@value, @startPos, @endPos - @startPos) end set @position = @position - 1 end return null end 

e l’uso:

 select dbo.GetStringPartByDelimeter ('a;b;c;d;e', ';', 3) 

che restituisce:

 c 

Prova questo:

 CREATE function [SplitWordList] ( @list varchar(8000) ) returns @t table ( Word varchar(50) not null, Position int identity(1,1) not null ) as begin declare @pos int, @lpos int, @item varchar(100), @ignore varchar(100), @dl int, @a1 int, @a2 int, @z1 int, @z2 int, @n1 int, @n2 int, @c varchar(1), @a smallint select @a1 = ascii('a'), @a2 = ascii('A'), @z1 = ascii('z'), @z2 = ascii('Z'), @n1 = ascii('0'), @n2 = ascii('9') set @ignore = '''"' set @pos = 1 set @dl = datalength(@list) set @lpos = 1 set @item = '' while (@pos <= @dl) begin set @c = substring(@list, @pos, 1) if (@ignore not like '%' + @c + '%') begin set @a = ascii(@c) if ((@a >= @a1) and (@a <= @z1)) or ((@a >= @a2) and (@a <= @z2)) or ((@a >= @n1) and (@a <= @n2)) begin set @item = @item + @c end else if (@item > '') begin insert into @t values (@item) set @item = '' end end set @pos = @pos + 1 end if (@item > '') begin insert into @t values (@item) end return end 

Provalo in questo modo:

 select * from SplitWordList('Hello John Smith') 

L’esempio seguente utilizza un CTE ricorsivo

Aggiornamento 18.09.2013

 CREATE FUNCTION dbo.SplitStrings_CTE(@List nvarchar(max), @Delimiter nvarchar(1)) RETURNS @returns TABLE (val nvarchar(max), [level] int, PRIMARY KEY CLUSTERED([level])) AS BEGIN ;WITH cte AS ( SELECT SUBSTRING(@List, 0, CHARINDEX(@Delimiter, @List + @Delimiter)) AS val, CAST(STUFF(@List + @Delimiter, 1, CHARINDEX(@Delimiter, @List + @Delimiter), '') AS nvarchar(max)) AS stval, 1 AS [level] UNION ALL SELECT SUBSTRING(stval, 0, CHARINDEX(@Delimiter, stval)), CAST(STUFF(stval, 1, CHARINDEX(@Delimiter, stval), '') AS nvarchar(max)), [level] + 1 FROM cte WHERE stval != '' ) INSERT @returns SELECT REPLACE(val, ' ','' ) AS val, [level] FROM cte WHERE val > '' RETURN END 

Demo su SQLFiddle

 Alter Function dbo.fn_Split ( @Expression nvarchar(max), @Delimiter nvarchar(20) = ',', @Qualifier char(1) = Null ) RETURNS @Results TABLE (id int IDENTITY(1,1), value nvarchar(max)) AS BEGIN /* USAGE Select * From dbo.fn_Split('apple pear grape banana orange honeydew cantalope 3 2 1 4', ' ', Null) Select * From dbo.fn_Split('1,abc,"Doe, John",4', ',', '"') Select * From dbo.fn_Split('Hello 0,"&""&&&&', ',', '"') */ -- Declare Variables DECLARE @X xml, @Temp nvarchar(max), @Temp2 nvarchar(max), @Start int, @End int -- HTML Encode @Expression Select @Expression = (Select @Expression For XML Path('')) -- Find all occurences of @Delimiter within @Qualifier and replace with 

***

While PATINDEX(‘%’ + @Qualifier + ‘%’, @Expression) > 0 AND Len(IsNull(@Qualifier, ”)) > 0 BEGIN Select — Starting character position of @Qualifier @Start = PATINDEX(‘%’ + @Qualifier + ‘%’, @Expression), — @Expression starting at the @Start position @Temp = SubString(@Expression, @Start + 1, LEN(@Expression)[email protected]+1), — Next position of @Qualifier within @Expression @End = PATINDEX(‘%’ + @Qualifier + ‘%’, @Temp) – 1, — The part of Expression found between the @Qualifiers @Temp2 = Case When @End < 0 Then @Temp Else Left(@Temp, @End) End, -- New @Expression @Expression = REPLACE(@Expression, @Qualifier + @Temp2 + Case When @End < 0 Then '' Else @Qualifier End, Replace(@Temp2, @Delimiter, '

***

‘) ) END — Replace all occurences of @Delimiter within @Expression with ‘‘ — And convert it to XML so we can select from it SET @X = Cast(‘‘ + Replace(@Expression, @Delimiter, ‘‘) + ‘‘ as xml) — Insert into our returnable table replacing ‘

***

‘ back to @Delimiter INSERT @Results SELECT “Value” = LTRIM(RTrim(Replace(C.value(‘.’, ‘nvarchar(max)’), ‘

***

‘, @Delimiter))) FROM @X.nodes(‘fn_Split’) as X(C) — Return our temp table RETURN END

Se il tuo database ha un livello di compatibilità di 130 o superiore, puoi utilizzare la funzione STRING_SPLIT insieme alle clausole OFFSET FETCH per ottenere l’elemento specifico per indice.

Per ottenere l’articolo all’indice N (a base zero), è ansible utilizzare il seguente codice

 SELECT value FROM STRING_SPLIT('Hello John Smith',' ') ORDER BY (SELECT NULL) OFFSET N ROWS FETCH NEXT 1 ROWS ONLY 

Per verificare il livello di compatibilità del tuo database , esegui questo codice:

 SELECT compatibility_level FROM sys.databases WHERE name = 'YourDBName'; 

So che è una vecchia domanda, ma penso che qualcuno possa beneficiare della mia soluzione.

 select SUBSTRING(column_name,1,CHARINDEX(' ',column_name,1)-1) ,SUBSTRING(SUBSTRING(column_name,CHARINDEX(' ',column_name,1)+1,LEN(column_name)) ,1 ,CHARINDEX(' ',SUBSTRING(column_name,CHARINDEX(' ',column_name,1)+1,LEN(column_name)),1)-1) ,SUBSTRING(SUBSTRING(column_name,CHARINDEX(' ',column_name,1)+1,LEN(column_name)) ,CHARINDEX(' ',SUBSTRING(column_name,CHARINDEX(' ',column_name,1)+1,LEN(column_name)),1)+1 ,LEN(column_name)) from table_name 

SQL FIDDLE

vantaggi:

  • Separa tutto il deliminatore delle 3 stringhe di “”.
  • Non si deve usare il ciclo mentre questo diminuisce le prestazioni.
  • Non è necessario ruotare perché tutta la sottostringa risultante verrà visualizzata in una riga

limitazioni:

  • Uno deve sapere il no totale. di spazi (sottostringa).

Nota : la soluzione può fornire sottostringa fino a N.

Per superare la limitazione possiamo usare il seguente rif .

Ma ancora una volta la soluzione di cui sopra non può essere utilizzata in una tabella (Actaully non era in grado di usarlo).

Ancora una volta spero che questa soluzione possa aiutare qualcuno.

Aggiornamento: in caso di record> 50000 non è consigliabile utilizzare LOOPS in quanto peggiorerà le prestazioni

Quasi tutte le altre risposte codice diviso stanno sostituendo la stringa che viene divisa, che spreca i cicli della CPU ed esegue allocazioni di memoria non necessarie.

Copro un modo molto migliore per fare uno split string qui: http://www.digitalruby.com/split-string-sql-server/

Ecco il codice:

 SET NOCOUNT ON -- You will want to change nvarchar(MAX) to nvarchar(50), varchar(50) or whatever matches exactly with the string column you will be searching against DECLARE @SplitStringTable TABLE (Value nvarchar(MAX) NOT NULL) DECLARE @StringToSplit nvarchar(MAX) = 'your|string|to|split|here' DECLARE @SplitEndPos int DECLARE @SplitValue nvarchar(MAX) DECLARE @SplitDelim nvarchar(1) = '|' DECLARE @SplitStartPos int = 1 SET @SplitEndPos = CHARINDEX(@SplitDelim, @StringToSplit, @SplitStartPos) WHILE @SplitEndPos > 0 BEGIN SET @SplitValue = SUBSTRING(@StringToSplit, @SplitStartPos, (@SplitEndPos - @SplitStartPos)) INSERT @SplitStringTable (Value) VALUES (@SplitValue) SET @SplitStartPos = @SplitEndPos + 1 SET @SplitEndPos = CHARINDEX(@SplitDelim, @StringToSplit, @SplitStartPos) END SET @SplitValue = SUBSTRING(@StringToSplit, @SplitStartPos, 2147483647) INSERT @SplitStringTable (Value) VALUES(@SplitValue) SET NOCOUNT OFF -- You can select or join with the values in @SplitStringTable at this point. 

È ansible dividere una stringa in SQL senza bisogno di una funzione:

 DECLARE @bla varchar(MAX) SET @bla = 'BED40DFC-F468-46DD-8017-00EF2FA3E4A4,64B59FC5-3F4D-4B0E-9A48-01F3D4F220B0,A611A108-97CA-42F3-A2E1-057165339719,E72D95EA-578F-45FC-88E5-075F66FD726C' -- http://stackoverflow.com/questions/14712864/how-to-query-values-from-xml-nodes SELECT x.XmlCol.value('.', 'varchar(36)') AS val FROM ( SELECT CAST('' + REPLACE(@bla, ',', '') + '' AS xml) AS RawXml ) AS b CROSS APPLY b.RawXml.nodes('e') x(XmlCol); 

Se devi supportare stringhe arbitrarie (con caratteri speciali xml)

 DECLARE @bla NVARCHAR(MAX) SET @bla = 'unsafe & safe Utf8CharsDon''tGetEncoded ÄöÜ - "Conex",Barnes & Noble,abc,def,ghi' -- http://stackoverflow.com/questions/14712864/how-to-query-values-from-xml-nodes SELECT x.XmlCol.value('.', 'nvarchar(MAX)') AS val FROM ( SELECT CAST('' + REPLACE((SELECT @bla FOR XML PATH('')), ',', '') + '' AS xml) AS RawXml ) AS b CROSS APPLY b.RawXml.nodes('e') x(XmlCol); 

Soluzione basata su set puro che utilizza TVF con CTE ricorsivo. È ansible JOIN e APPLY questa funzione a qualsiasi set di dati.

 create function [dbo].[SplitStringToResultSet] (@value varchar(max), @separator char(1)) returns table as return with r as ( select value, cast(null as varchar(max)) [x], -1 [no] from (select rtrim(cast(@value as varchar(max))) [value]) as j union all select right(value, len(value)-case charindex(@separator, value) when 0 then len(value) else charindex(@separator, value) end) [value] , left(r.[value], case charindex(@separator, r.value) when 0 then len(r.value) else abs(charindex(@separator, r.[value])-1) end ) [x] , [no] + 1 [no] from r where value > '') select ltrim(x) [value], [no] [index] from r where x is not null; go 

Uso:

 select * from [dbo].[SplitStringToResultSet]('Hello John Smith', ' ') where [index] = 1; 

Risultato:

 value index ------------- John 1 

A partire da SQL Server 2016 abbiamo string_split

 DECLARE @string varchar(100) = 'Richard, Mike, Mark' SELECT value FROM string_split(@string, ',') 

Un approccio moderno che utilizza STRING_SPLIT richiede SQL Server 2016 e versioni successive.

 DECLARE @string varchar(100) = 'Hello John Smith' SELECT ROW_NUMBER() OVER (ORDER BY value) AS RowNr, value FROM string_split(@string, ' ') 

Risultato:

 RowNr value 1 Hello 2 John 3 Smith 

Ora è ansible ottenere l’ennesimo elemento dal numero di riga.

La risposta di Aaron Bertrand è grande, ma imperfetta. Non gestisce con precisione uno spazio come delimitatore (come era l’esempio nella domanda originale) poiché la funzione di lunghezza elimina gli spazi finali.

Quello che segue è il suo codice, con una piccola regolazione per consentire un delimitatore di spazio:

 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+'x')-1) = @Delim ) AS y ); 

Here is a function that will accomplish the question’s goal of splitting a string and accessing item X:

 CREATE FUNCTION [dbo].[SplitString] ( @List VARCHAR(MAX), @Delimiter VARCHAR(255), @ElementNumber INT ) RETURNS VARCHAR(MAX) AS BEGIN DECLARE @inp VARCHAR(MAX) SET @inp = (SELECT REPLACE(@List,@Delimiter,'_DELMTR_') FOR XML PATH('')) DECLARE @xml XML SET @xml = '' + REPLACE(@inp,'_DELMTR_','') + '' 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 

Uso:

 SELECT dbo.SplitString('Hello John Smith', ' ', 2) 

Risultato:

 John 

SIMPLE SOLUTION FOR PARSING FIRST AND LAST NAME

 DECLARE @Name varchar(10) = 'John Smith' -- Get First Name SELECT SUBSTRING(@Name, 0, (SELECT CHARINDEX(' ', @Name))) -- Get Last Name SELECT SUBSTRING(@Name, (SELECT CHARINDEX(' ', @Name)) + 1, LEN(@Name)) 

In my case (and in many others it seems…), I have a list of first and last names separated by a single space. This can be used directly inside a select statement to parse first and last name.

 -- ie Get First and Last Name from a table of Full Names SELECT SUBSTRING(FullName, 0, (SELECT CHARINDEX(' ', FullName))) as FirstName, SUBSTRING(FullName, (SELECT CHARINDEX(' ', FullName)) + 1, LEN(FullName)) as LastName, From FullNameTable 

Well, mine isn’t all that simpler, but here is the code I use to split a comma-delimited input variable into individual values, and put it into a table variable. I’m sure you could modify this slightly to split based on a space and then to do a basic SELECT query against that table variable to get your results.

 -- Create temporary table to parse the list of accounting cycles. DECLARE @tblAccountingCycles table ( AccountingCycle varchar(10) ) DECLARE @vchAccountingCycle varchar(10) DECLARE @intPosition int SET @vchAccountingCycleIDs = LTRIM(RTRIM(@vchAccountingCycleIDs)) + ',' SET @intPosition = CHARINDEX(',', @vchAccountingCycleIDs, 1) IF REPLACE(@vchAccountingCycleIDs, ',', '') <> '' BEGIN WHILE @intPosition > 0 BEGIN SET @vchAccountingCycle = LTRIM(RTRIM(LEFT(@vchAccountingCycleIDs, @intPosition - 1))) IF @vchAccountingCycle <> '' BEGIN INSERT INTO @tblAccountingCycles (AccountingCycle) VALUES (@vchAccountingCycle) END SET @vchAccountingCycleIDs = RIGHT(@vchAccountingCycleIDs, LEN(@vchAccountingCycleIDs) - @intPosition) SET @intPosition = CHARINDEX(',', @vchAccountingCycleIDs, 1) END END 

The concept is pretty much the same. One other alternative is to leverage the .NET compatibility within SQL Server 2005 itself. You can essentially write yourself a simple method in .NET that would split the string and then expose that as a stored procedure/function.

This is something I did in order to get a specific token in a string. (Tested in MSSQL 2008)

First, creating the following functions: (found in: here

 CREATE FUNCTION dbo.SplitStrings_Moden ( @List NVARCHAR(MAX), @Delimiter NVARCHAR(255) ) RETURNS TABLE WITH SCHEMABINDING AS RETURN WITH E1(N) AS ( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1), E2(N) AS (SELECT 1 FROM E1 a, E1 b), E4(N) AS (SELECT 1 FROM E2 a, E2 b), E42(N) AS (SELECT 1 FROM E4 a, E2 b), cteTally(N) AS (SELECT 0 UNION ALL SELECT TOP (DATALENGTH(ISNULL(@List,1))) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E42), cteStart(N1) AS (SELECT t.N+1 FROM cteTally t WHERE (SUBSTRING(@List,tN,1) = @Delimiter OR tN = 0)) SELECT Item = SUBSTRING(@List, s.N1, ISNULL(NULLIF(CHARINDEX(@Delimiter,@List,s.N1),0)-s.N1,8000)) FROM cteStart s; 

e

 create FUNCTION dbo.getToken ( @List NVARCHAR(MAX), @Delimiter NVARCHAR(255), @Pos int ) RETURNS varchar(max) as begin declare @returnValue varchar(max); select @returnValue = tbl.Item from ( select ROW_NUMBER() over (order by (select null)) as id, * from dbo.SplitStrings_Moden(@List, @Delimiter) ) as tbl where tbl.id = @Pos return @returnValue end 

then you can use it like that:

 select dbo.getToken('1111_2222_3333_', '_', 1) 

which return 1111