Como divido uma string para poder acessar o item x?

Usando o SQL Server, como divido uma string para poder acessar o item x?

Pegue uma string “Hello John Smith”. Como posso dividir a string pelo espaço e acessar o item no índice 1, que deve retornar “John”?

Você pode encontrar a solução em Função Definida pelo Usuário SQL para Analisar uma Cadeia Delimitada (do The Code Project ).

Você pode usar essa lógica simples:

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 

Não acredito que o SQL Server tenha uma function de divisão interna, portanto, além de uma UDF, a única outra resposta que sei é seqüestrar a function PARSENAME:

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

PARSENAME pega uma string e a divide no caractere de período. Leva um número como seu segundo argumento, e esse número especifica qual segmento da string retornar (trabalhando de trás para frente).

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

Problema óbvio é quando a string já contém um período. Eu ainda acho que usar um UDF é a melhor maneira … alguma outra sugestão?

Primeiro, crie uma function (usando CTE, a expressão de tabela comum elimina a necessidade de uma tabela temporária)

  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 

Em seguida, use-o como qualquer tabela (ou modifique-o para caber dentro do seu procedimento armazenado existente) dessa forma.

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

Atualizar

A versão anterior falharia na sequência de input com mais de 4000 caracteres. Esta versão cuida da limitação:

 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 

O uso permanece o mesmo.

A maioria das soluções aqui usa loops while ou CTEs recursivos. Uma abordagem baseada em conjuntos será superior, eu prometo:

 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

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

Você pode aproveitar uma tabela Number para fazer a análise de string.

Crie uma tabela de números físicos:

  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 

Criar tabela de teste com linhas de 1000000

  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 

Crie a function

  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 (saídas de 3mil linhas em 40s no meu laptop)

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

Limpar

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

O desempenho aqui não é incrível, mas chamar uma function com mais de um milhão de linhas não é a melhor ideia. Se executar uma string dividida em várias linhas, eu evitaria a function.

Aqui está um UDF que fará isso. Ele retornará uma tabela dos valores delimitados, não tentou todos os cenários, mas seu exemplo funciona bem.

 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 

Você chamaria assim:

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

Editar: Solução atualizada para lidar com delimitadores com len> 1 como em:

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

Nenhum código, mas leia o artigo definitivo sobre isso. Todas as soluções em outras respostas são versões das listadas neste artigo: Matrizes e listas no SQL Server 2005 e além

Pessoalmente, eu usei uma solução de tabela do Numbers na maioria das vezes porque combina com o que eu tenho que fazer …

Aqui eu publico uma maneira simples de solução

 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 

Execute a function como esta

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

Esta questão não é sobre uma abordagem de divisão de cadeia , mas sobre como obter o enésimo elemento .

Todas as respostas aqui estão fazendo algum tipo de divisão de string usando recursion, CHARINDEX , múltiplos CHARINDEX , REVERSE e PATINDEX , inventando funções, chamando methods CLR, tabelas numéricas, CROSS APPLY s … A maioria das respostas cobre muitas linhas de código.

Mas – se você realmente não quer nada mais do que uma abordagem para obter o enésimo elemento – isso pode ser feito como um one-liner real , sem UDF, nem mesmo um sub-select … E como um benefício extra: digite safe

Obtenha 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 (especialmente um entre &>< ), 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)'); 

Na minha opinião, vocês estão complicando demais. Basta criar um CLR UDF e terminar com isso.

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

Que tal usar string e values() declaração?

 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 

Resultado definido.

 id item 1 Hello 2 John 3 Smith 

Esse padrão funciona bem e você pode generalizar

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

note FIELD , INDEX e TYPE .

Deixe alguma tabela com identificadores como

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

Então você pode escrever

 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 

dividindo e lançando todas as partes.

Eu uso a resposta da frederic mas isso não funcionou no SQL Server 2005

Eu modifiquei e estou usando select com union all e funciona

 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 o conjunto de resultados é:

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

Eu estava procurando a solução na net e o abaixo funciona para mim. Ref .

E você chama a function assim:

 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 

Ainda outro obter n’th parte da cadeia de caracteres por function delimetro:

 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 o uso:

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

que retorna:

 c 

Tente isto:

 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 

Teste assim:

 select * from SplitWordList('Hello John Smith') 

O exemplo a seguir usa um CTE recursivo

Atualização 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 

Demonstração no 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)-@Start+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 o seu database tiver um nível de compatibilidade de 130 ou superior, você poderá usar a function STRING_SPLIT junto com as cláusulas OFFSET FETCH para obter o item específico por índice.

Para obter o item no índice N (baseado em zero), você pode usar o seguinte código

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

Para verificar o nível de compatibilidade do seu database , execute este código:

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

Eu sei que é uma pergunta antiga, mas acho que alguém pode se beneficiar da minha solução.

 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

Vantagens:

  • Ele separa todos os três sub-strings deliminator por ”.
  • Não se deve usar loop while, pois diminui o desempenho.
  • Não há necessidade de dinamizar, pois toda a sub-string resultante será exibida em uma linha

Limitações:

  • É preciso saber o total não. de espaços (sub-string).

Nota : a solução pode fornecer sub-string até N.

Para superar a limitação, podemos usar a seguinte ref .

Mas, novamente, a solução acima não pode ser usada em uma tabela (Actaully i não foi capaz de usá-lo).

Mais uma vez espero que esta solução possa ajudar alguém.

Atualização: No caso de registros> 50000, não é aconselhável usar o LOOPS pois isso prejudicará o desempenho

Quase todas as outras respostas dividindo o código estão substituindo a string que está sendo dividida, o que desperdiça os ciclos da CPU e realiza alocações de memory desnecessárias.

Eu cubro uma maneira muito melhor de fazer uma string dividida aqui: http://www.digitalruby.com/split-string-sql-server/

Aqui está o código:

 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. 

Você pode dividir uma string no SQL sem precisar de uma function:

 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 você precisar dar suporte a cadeias arbitrárias (com caracteres especiais 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); 

Solução baseada em conjunto puro usando TVF com CTE recursivo. Você pode se JOIN e APPLY essa function a qualquer dataset.

 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; 

Resultado:

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

Começando com o SQL Server 2016 nós string_split

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

Uma abordagem moderna usando o STRING_SPLIT requer o SQL Server 2016 e superior.

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

Resultado:

 RowNr value 1 Hello 2 John 3 Smith 

Agora é possível obter o enésimo elemento do número da linha.

A resposta de Aaron Bertrand é ótima, mas falha. Ele não manipula com precisão um espaço como um delimitador (como foi o exemplo na pergunta original), pois a function length retira espaços à direita.

O seguinte é seu código, com um pequeno ajuste para permitir um delimitador de espaço:

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

Aqui está uma function que realizará o objective da pergunta de dividir uma string e acessar o 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) 

Resultado:

 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