Jak podzielić ciąg znaków, aby uzyskać dostęp do elementu x?

Nie wierzę, że SQL Server ma wbudowaną funkcję dzielenia, więc poza UDF, jedyną znaną mi odpowiedzią jest przejęcie funkcji PARSENAME:

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

PARSENAME pobiera łańcuch znaków i dzieli go na znak kropki. Przyjmuje liczbę jako drugi argument i ta liczba określa, który segment łańcucha znaków ma zostać zwrócony (działa od tyłu do przodu).

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

Oczywistym problemem jest to, że łańcuch zawiera kropkę. Nadal uważam, że używanie UDF jest najlepsze sposób...jakieś inne sugestie?

Najpierw Utwórz funkcję (używając CTE, common table expression usuwa potrzebę tworzenia tabeli tymczasowej)

 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 b-a ELSE 4000 end) 
            AS s
        from tokens
      )
    GO

Następnie użyj go jako dowolnej tabeli (lub zmodyfikuj ją tak, aby pasowała do istniejącego przechowywanego proc) w ten sposób.

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

Update

Poprzednia wersja zawiedzie Dla ciągu wejściowego dłuższego niż 4000 znaków. Ta wersja dba o ograniczenie:

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 b-a ELSE LEN(@str) end) 
    AS s
from tokens
);

GO

Użycie pozostaje takie samo.

Większość rozwiązań tutaj używa pętli while lub rekurencyjnych CTE. Podejście oparte na zestawie będzie lepsze, obiecuję:

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

Więcej na temat funkcji dzielonych, dlaczego (i dowód, że) podczas gdy pętle i rekurencyjne CTE nie skalują się, i lepsze alternatywy, jeśli dzielenie ciągów pochodzących z aplikacji Warstwa:

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

Możesz użyć tabeli liczb, aby wykonać parsowanie łańcuchów.

Utwórz tabelę liczb fizycznych:

    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

Utwórz tabelę testową z 1000000 wierszy

    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

Utwórz funkcję

    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

Użycie (wyprowadza 3mil wierszy w 40s na moim laptopie)

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

Oczyszczanie

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

Wydajność tutaj nie jest niesamowite, ale wywołanie funkcji ponad milion wierszy tabeli nie jest najlepszym pomysłem. Jeśli wykonujesz ciąg znaków podzielony na wiele wierszy, uniknęłabym tej funkcji.

Oto UDF, który to zrobi. Zwróci tabelę rozdzielonych wartości, nie wypróbowałem na niej wszystkich scenariuszy, ale twój przykład działa dobrze.

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

Nazwałbyś to tak:

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

Edit: zaktualizowane rozwiązanie do obsługi ograniczników z Len > 1 Jak w:

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

Tutaj zamieszczam prosty sposób rozwiązania

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'<t>' + REPLACE(@delimited,@delimiter,'</t><t>') + '</t>'

          INSERT INTO @t(val)
          SELECT  r.value('.','varchar(MAX)') as item
          FROM  @xml.nodes('/t') as records(r)
          RETURN
        END

Wykonaj funkcję w ten sposób

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

To pytanie dotyczy nie podejścia do podziału łańcuchów , ale Jak uzyskać n-ty element .

Wszystkie odpowiedzi tutaj wykonują pewnego rodzaju podział łańcuchów za pomocą rekurencji, CTEs, wielokrotne CHARINDEX, REVERSE i PATINDEX, wymyślanie funkcji, wywoływanie metod CLR, tabele liczb, CROSS APPLYs ... Większość odpowiedzi obejmuje wiele linii kodu.

Ale-jeśli naprawdę chcesz nic więcej niż podejście do uzyskania n - tego elementu - można to zrobić jako prawdziwe one-liner, bez UDF, nawet pod-select... I jako dodatkową korzyść: typ Bezpieczny

Pobierz część 2 oddzieloną spacją:

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

Oczywiście możesz użyć zmiennych dla ogranicznika i pozycji (Użyj sql:column, aby pobrać pozycję bezpośrednio z wartości zapytania):

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

Jeśli twój łańcuch znaków może zawierać zakazane znaki (szczególnie jeden spośród &><), nadal możesz to zrobić w ten sposób. Wystarczy najpierw użyć FOR XML PATH na swoim łańcuchu, aby zastąpić wszystkie zakazane znaki z dopasowaną sekwencją ucieczki bezwarunkowo.

Jest to bardzo szczególny przypadek, jeśli - dodatkowo - Twoim ogranicznikiem jest średnik . W tym przypadku najpierw zamieniam ogranicznik na ' # DLMT#', a na końcu zamieniam go na znaczniki XML:

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

Niestety deweloperzy zapomnieli zwrócić indeks części za pomocą STRING_SPLIT. Ale, używając SQL-Server 2016+, jest OPENJSON.

Dokumentacja stwierdza wyraźnie:

Gdy openjson przetwarza tablicę JSON, funkcja zwraca indeksy elementów w tekście JSON jako klucze.

Łańcuch taki jak 1,2,3 nie potrzebuje nic więcej niż nawiasy: [1,2,3].
Ciąg słów jak this is an example musi być ["this","is","an"," example"].
Są to bardzo proste operacje ciągów. Po prostu wypróbuj:

DECLARE @str VARCHAR(100)='Hello John Smith';

SELECT [value]
FROM OPENJSON('["' + REPLACE(@str,' ','","') + '"]')
WHERE [key]=1 --zero-based!

Moim zdaniem to zbyt skomplikowane. Wystarczy utworzyć CLR UDF i zakończyć z nim.

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<string> SearchWords = new List<string>();
    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()));
  }
};

A co z użyciem string i 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

Wynik-zestaw osiągnięty.

id  item
1   Hello
2   John
3   Smith

Ten wzór działa dobrze i można uogólnić

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

Uwaga Pole, indeks iTyp .

Niech jakaś tabela z identyfikatorami jak

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

Wtedy możesz napisać

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,'<n>'+Replace(fieldName,'.','</n><n>')+'</n>')
         FROM     some_TABLE
       ) Q

Dzielenie i odlewanie wszystkich części.

Używam odpowiedzi frederic ale to nie działa w SQL Server 2005

Zmodyfikowałem go i używam select z union all i działa

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

A wynik-set to:

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

Szukałem rozwiązania na necie i poniższe działa dla mnie. Ref .

I wywołujesz funkcję w ten sposób :

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

Yet another get n'th part of string by delimeter function:

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

I użycie:

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

Które zwraca:

c

Spróbuj tego:

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

Przetestuj to tak:

select * from SplitWordList('Hello John Smith')

Poniższy przykład używa rekurencyjnego CTE

Aktualizacja 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 na SQLFiddle

Jeśli twoja baza danych ma poziom zgodności 130 lub wyższy, możesz użyć funkcji STRING_SPLIT wraz z klauzulami OFFSET FETCH , Aby uzyskać określony element według indeksu.

Aby uzyskać pozycję w index N (na podstawie zera), możesz użyć następującego kodu

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

Aby sprawdzić poziom zgodności bazy danych , wykonaj ten kod:

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

    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 &LT 0 Then @Temp Else Left(@Temp, @End) End,
             -- New @Expression
             @Expression = REPLACE(@Expression,
                                   @Qualifier + @Temp2 + Case When @End &LT 0 Then '' Else @Qualifier End,
                                   Replace(@Temp2, @Delimiter, '|||***|||')
                           )
       END

       -- Replace all occurences of @Delimiter within @Expression with '&lt/fn_Split&gt&ltfn_Split&gt'
       -- And convert it to XML so we can select from it
       SET
          @X = Cast('&ltfn_Split&gt' +
                    Replace(@Expression, @Delimiter, '&lt/fn_Split&gt&ltfn_Split&gt') +
                    '&lt/fn_Split&gt' 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

Wiem, że to stare pytanie, ale myślę, że ktoś może skorzystać z mojego rozwiązania.

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

Zalety:

• oddziela wszystkie 3 podzbiory deliminatora przez '".
• nie wolno używać pętli while, ponieważ zmniejsza to wydajność.
• nie ma potrzeby obracania się, ponieważ wszystkie wynikowe podciągi będą wyświetlane w jeden rząd

Ograniczenia:

• trzeba znać całkowity nie. z spacje (sub-string).

Uwaga : rozwiązanie może dać ciąg podrzędny do N.

Aby przezwyciężyć ograniczenie możemy użyć następujących ref.

Ale znowu powyższe Rozwiązanie nie może być używany w tabeli (ACTA nie byłem w stanie go użyć).

Update: W przypadku rekordów > 50000 nie jest wskazane używać LOOPS Jak będzie obniżyć wydajność

Prawie wszystkie pozostałe odpowiedzi dzielone kod zastępują dzielony ciąg znaków, który marnuje cykle procesora i wykonuje niepotrzebne alokacje pamięci.

Opisuję znacznie lepszy sposób na podział strun tutaj: http://www.digitalruby.com/split-string-sql-server/

Oto kod:

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.

Możesz podzielić ciąg znaków w SQL bez potrzeby użycia funkcji:

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('<e>' + REPLACE(@bla, ',', '</e><e>') + '</e>' AS xml) AS RawXml
) AS b 
CROSS APPLY b.RawXml.nodes('e') x(XmlCol);

Jeśli chcesz obsługiwać dowolne ciągi znaków (ze znakami specjalnymi xml)

DECLARE @bla NVARCHAR(MAX)
SET @bla = '<html>unsafe & safe Utf8CharsDon''tGetEncoded ÄöÜ - "Conex"<html>,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('<e>' + REPLACE((SELECT @bla FOR XML PATH('')), ',', '</e><e>') + '</e>' AS xml) AS RawXml
) AS b 
CROSS APPLY b.RawXml.nodes('e') x(XmlCol); 

Czyste rozwiązanie oparte na zbiorze przy użyciu TVF z rekurencyjnym CTE. Możesz JOIN i APPLY tej funkcji do dowolnego zbioru danych.

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

Użycie:

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

Wynik:

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

Począwszy od SQL Server 2016 we string_split

DECLARE @string varchar(100) = 'Richard, Mike, Mark'

SELECT value FROM string_split(@string, ',')

Nowoczesne podejście wykorzystujące STRING_SPLIT , wymaga SQL Server 2016 i nowszych.

DECLARE @string varchar(100) = 'Hello John Smith'

SELECT
    ROW_NUMBER() OVER (ORDER BY value) AS RowNr,
    value
FROM string_split(@string, ' ')

Wynik:

RowNr   value
1       Hello
2       John
3       Smith

Teraz można uzyskać ten n-ty element z numeru wiersza.

Odpowiedź Aarona Bertranda jest świetna, ale błędna. Nie obsługuje dokładnie spacji jako ogranicznika (jak to było w przykładzie w pytaniu pierwotnym), ponieważ funkcja długości Usuwa spacje końcowe.

Poniżej znajduje się jego kod, z małą korektą pozwalającą na ogranicznik spacji:

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

Oto funkcja, która osiągnie cel pytania, jakim jest rozdzielenie łańcucha znaków i dostęp do elementu 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 = '<split><el>' + REPLACE(@inp,'_DELMTR_','</el><el>') + '</el></split>'

       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

Użycie:

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

Wynik:

John

PROSTE ROZWIĄZANIE DO ANALIZY IMIENIA I NAZWISKA

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

W moim przypadku (i w wielu innych wydaje się...), Mam listę imion i nazwisk oddzielonych pojedynczą spacją. Można tego użyć bezpośrednio wewnątrz instrukcji select do analizy imienia i nazwiska.

-- i.e. 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

Wiem, że jest późno, ale ostatnio miałem ten wymóg i wymyśliłem poniższy kod. Nie mam wyboru, aby użyć funkcji zdefiniowanej przez użytkownika. Mam nadzieję, że to pomoże.

Wybierz Substrat( SUBSTRING ('Hello John Smith', 0, CHARINDEX ('', 'Hello John Smith', CHARINDEX (' ' , 'Hello John Smith')+1) ), CHARINDEX ( '' , 'Hello John Smith'), LEN ('Hello John Smith') )

Cóż, mój nie jest wcale taki prosty, ale oto kod, którego używam do dzielenia zmiennej wejściowej rozdzielanej przecinkami na poszczególne wartości i umieszczania jej w zmiennej tabelkowej. Jestem pewien, że można zmodyfikować to nieco podzielić na podstawie spacji, a następnie zrobić podstawowe zapytanie SELECT przeciwko tej zmiennej tabeli, aby uzyskać wyniki.

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

Koncepcja jest prawie taka sama. Inną alternatywą jest wykorzystanie zgodności. NET w SQL Server 2005. W zasadzie można pisać yourself prostą metodą w. NET, która podzieliłaby łańcuch znaków, a następnie ujawniłaby go jako procedurę/funkcję składowaną.

To jest coś, co zrobiłem, aby uzyskać konkretny token w łańcuchu. (Testowane w MSSQL 2008)

Najpierw tworzenie następujących funkcji: (znalezionych w: tutaj

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,t.N,1) = @Delimiter OR t.N = 0))
  SELECT Item = SUBSTRING(@List, s.N1, ISNULL(NULLIF(CHARINDEX(@Delimiter,@List,s.N1),0)-s.N1,8000))
    FROM cteStart s;

I

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

Wtedy możesz go używać w ten sposób:

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

Które zwracają 1111