Jak podzielić ciąg, dzięki czemu można uzyskać dostęp do elementu X?

głosy
442

Przy użyciu programu SQL Server, w jaki sposób podzielić ciąg, dzięki czemu można uzyskać dostęp do elementu X?

Weźmy ciąg „Witaj John Smith”. W jaki sposób można podzielić ciąg przez przestrzeń i uzyskać dostęp do elementu o indeksie 1, który powinien wrócić „John”?

Utwórz 05/08/2008 o 19:15
źródło użytkownik
W innych językach...                            


43 odpowiedzi

głosy
335

Nie wierzę, że SQL Server posiada wbudowaną funkcję podziału, tak inny niż UDF, jedyna inna odpowiedź Wiem tylko porwać funkcję PARSENAME:

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

PARSENAME pobiera ciąg i dzieli go na charakter epoki. To trwa kilka jako drugi argument, że liczba określa, które wrócić segment łańcucha (działa od tyłu do przodu).

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

Oczywistym problemem jest, gdy ciąg zawiera już okres. Nadal uważam, że za pomocą UDF jest najlepszym sposobem ... jakieś inne propozycje?

Odpowiedział 05/08/2008 o 19:45
źródło użytkownik

głosy
177

Można znaleźć rozwiązanie w SQL User Defined Function do analizowania Delimited String pomocny (z The Code Project ).

Można użyć tej prostej logiki:

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
Odpowiedział 05/08/2008 o 19:28
źródło użytkownik

głosy
106

Po pierwsze, należy utworzyć funkcję (za pomocą CTE, ekspresja wspólny stół eliminuje potrzebę tabeli temp)

 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 go zmodyfikować, aby zmieścić się w istniejącym przechowywanych proc) w taki sposób.

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

Aktualizacja

Poprzednia wersja zawiedzie do ciągu wejściowego dłuższy niż 4000 znaków. Wersja ta zajmuje się między innymi:

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

Wykorzystanie pozostaje taka sama.

Odpowiedział 05/08/2008 o 19:57
źródło użytkownik

głosy
51

Większość z rozwiązaniami tutaj użyć podczas pętli lub rekurencyjnych CTE. Podejście oparte na zestaw będzie lepszy, 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 informacji na temat podziału funkcji, dlaczego (i dowodu), podczas gdy pętle i rekurencyjne CTE nie skalę oraz lepszych alternatyw, jeśli struny do przecinania pochodzące z warstwy aplikacji:

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

Odpowiedział 12/11/2013 o 18:16
źródło użytkownik

głosy
37

Można wykorzystać tabelę Numer zrobić parsowania ciąg.

Tworzenie fizycznego numery tabeli:

    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

Tworzenie tabeli testowej z wierszy 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

Tworzenie funkcji

    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

Wykorzystanie (wyjścia 3mil wiersze w 40s na moim laptopie)

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

sprzątać

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

Wydajność tutaj nie jest niesamowite, ale wywołanie funkcji nad stołem mln wiersz nie jest najlepszym pomysłem. W przypadku wykonywania ciąg podzielony na wiele wierszy wolałbym uniknąć funkcję.

Odpowiedział 27/10/2008 o 17:48
źródło użytkownik

głosy
20

Oto UDF, która to zrobi. Będzie to powrót do stołu ustalonych wartości, nie próbowałem wszystkie scenariusze na nim, 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

Można by nazwać tak:


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

Edycja: Zaktualizowany rozwiązanie do obsługi delimters z len> 1, jak w:


select * From SplitString('Hello**John**Smith','**')
Odpowiedział 05/08/2008 o 19:39
źródło użytkownik

głosy
16

Brak kodu, ale czytać ostateczne artykuł na ten temat. Wszystkie rozwiązania w innych odpowiedzi są smaki te wymienione w tym artykule: Tablice i list w SQL Server 2005 and Beyond

Osobiście używałem rozwiązanie Liczby stołowego najczęściej dlatego, że pasuje do tego, co mam robić ...

Odpowiedział 26/09/2010 o 14:44
źródło użytkownik

głosy
15

Tutaj mogę napisać 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 IGNORE  INTO @t(val)
          SELECT  r.value('.','varchar(MAX)') as item
          FROM  @xml.nodes('/t') as records(r)
          RETURN
        END


Wykonaj funkcję takiego

  select * from dbo.split('Hello John Smith',' ')
Odpowiedział 30/01/2013 o 10:41
źródło użytkownik

głosy
12

To pytanie jest nie na temat podejścia podzielonym strun , ale o jak dostać się n-ty element .

Wszystkie odpowiedzi są tu robi jakieś rozszczepienie łańcucha za pomocą rekurencji, CTEs, stwardnienie CHARINDEX, REVERSEi PATINDEX, wymyślając funkcje, zadzwoń do metod numerycznych CLR, stoły, CROSS APPLYs ... Większość odpowiedzi obejmują wiele linii kodu.

Ale - jeśli naprawdę chcemy nic więcej niż podejście, aby uzyskać n-ty element, - można to zrobić jak prawdziwy-liner , bez UDF, nawet sub-select ... A jako dodatkową korzyść: bezpieczne typ

Zdobądź część 2 ograniczoną 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żna użyć zmiennych dla separatora i pozycji (użyj sql:columnodzyskać pozycję bezpośrednio z wartości kwerendy):

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żeli ciąg może zawierać niedozwolone znaki (szczególnie jeden spośród &><), nadal można zrobić to w ten sposób. Wystarczy użyć FOR XML PATHna ciąg najpierw wymienić wszystkie niedozwolone znaki z dopasowania sekwencji ucieczki zastrzeżeń.

Jest to bardzo szczególny przypadek, gdy - dodatkowo - Twój separator jest średnik . W tym przypadku wymienić ogranicznik pierwszy „# DLMT #” i zastąpić to znaczniki XML wreszcie:

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)');
Odpowiedział 08/07/2016 o 20:41
źródło użytkownik

głosy
10

Co na temat korzystania stringi values()stwierdzeniem?

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 IGNORE  INTO @Splited
EXEC(@str)

SELECT * FROM @Splited

Wynik-set osiągnięty.

id  item
1   Hello
2   John
3   Smith
Odpowiedział 01/03/2013 o 17:26
źródło użytkownik

głosy
10

Moim zdaniem faceci robią to zbyt skomplikowane. Wystarczy utworzyć UDF CLR i być z nim zrobić.

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()));
  }
};
Odpowiedział 19/07/2012 o 22:46
źródło użytkownik

głosy
8

Wzór ten działa dobrze i można uogólniać

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

Uwaga FIELD , INDEX i TYPE .

Niech jakiś stół z identyfikatorów, takich jak

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

Następnie można 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

dzielenia i odlewania wszystkie części.

Odpowiedział 11/11/2014 o 14:31
źródło użytkownik

głosy
8

Używam odpowiedź Fryderyka ale to nie działa w SQL Server 2005

I zmodyfikowano go i używam selectz union alli 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 IGNORE  INTO @Splited
EXEC(@str)

SELECT * FROM @Splited

A wynik osadzone jest:

id  item
1   Hello
2   John
3   Smith
4   how
5   are
6   you
Odpowiedział 13/08/2013 o 16:11
źródło użytkownik

głosy
6

Jeszcze inna dostać n'th część łańcucha według funkcji separatorem:

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żytkowanie:

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

która zwraca:

c
Odpowiedział 08/01/2016 o 14:30
źródło użytkownik

głosy
6

Szukałem rozwiązania na netto i poniżej pracuje dla mnie. Ref .

I wywołać funkcję tak:

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 IGNORE  INTO @temptable(Items) VALUES(@slice)       
        SET @String = RIGHT(@String,len(@String) - @idx)       
        IF len(@String) = 0 break       
    END   
    RETURN       
END
Odpowiedział 20/11/2011 o 07:40
źródło użytkownik

głosy
5

W następującym przykładzie rekurencyjnych 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 IGNORE  @returns
  SELECT REPLACE(val, ' ','' ) AS val, [level]
  FROM cte
  WHERE val > ''
  RETURN
END

Demo na SQLFiddle

Odpowiedział 14/03/2013 o 11:18
źródło użytkownik

głosy
5

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

Przetestować go w ten sposób:

select * from SplitWordList('Hello John Smith')
Odpowiedział 05/08/2008 o 19:41
źródło użytkownik

głosy
3


    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 '</fn_Split><fn_Split>'
       -- And convert it to XML so we can select from it
       SET
          @X = Cast('<fn_Split>' +
                    Replace(@Expression, @Delimiter, '</fn_Split><fn_Split>') +
                    '</fn_Split>' as xml)

       -- Insert into our returnable table replacing '|||***|||' back to @Delimiter
       INSERT IGNORE  @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

Odpowiedział 05/11/2013 o 01:12
źródło użytkownik

głosy
2

Jeśli baza danych ma poziom kompatybilności 130 lub wyższej następnie można użyć STRING_SPLIT funkcję wraz z OFFSET FETCH klauzule, aby uzyskać żądaną pozycję przez indeks.

Aby uzyskać pozycję w indeksie 1, można użyć następującego kodu

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

Aby sprawdzić poziom zgodności bazy danych , należy wykonać ten kod:

SELECT compatibility_level  
FROM sys.databases WHERE name = 'YourDBName';
Odpowiedział 05/04/2018 o 10:23
źródło użytkownik

głosy
2

Można podzielić ciąg w SQL bez potrzeby funkcję:

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 trzeba obsługiwać dowolne ciągi znaków (XML znaków specjalnych)

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); 
Odpowiedział 23/10/2015 o 10:07
źródło użytkownik

głosy
2

Prawie wszystkie inne odpowiedzi podzielić kod wymieniasz łańcuch rozszczepiony których odpady cykli procesora i wykonuje niepotrzebne alokacji pamięci.

I obejmują znacznie lepszy sposób to zrobić split ciąg 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 IGNORE  @SplitStringTable (Value) VALUES (@SplitValue)
    SET @SplitStartPos = @SplitEndPos + 1
    SET @SplitEndPos = CHARINDEX(@SplitDelim, @StringToSplit, @SplitStartPos)
END

SET @SplitValue = SUBSTRING(@StringToSplit, @SplitStartPos, 2147483647)
INSERT IGNORE  @SplitStringTable (Value) VALUES(@SplitValue)

SET NOCOUNT OFF

-- You can select or join with the values in @SplitStringTable at this point.
Odpowiedział 26/08/2014 o 17:50
źródło użytkownik

głosy
2

Wiem, że to stare pytanie, ale myślę, że ktoś może korzystać 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 sub-strings deliminator przez ''.
  • Nie należy używać podczas pętli, ponieważ zmniejsza wydajność.
  • Nie ma potrzeby, obraca się wszystkimi wypadkowej podciąg będzie wyświetlany w jednym rzędzie

ograniczenia:

  • Trzeba wiedzieć całkowitą NO. przestrzeni (podciąg).

Uwaga : rozwiązanie może dać podnapis do N.

Aby przezwyciężył ograniczenia możemy użyć następującego ref .

Ale znowu powyższe rozwiązanie nie może być użyty w tabeli (actaully i nie był w stanie go używać).

Znowu mam nadzieję, to rozwiązanie może pomóc w jakiś jeden lat.

Aktualizacja: W przypadku Records> 50000 nie jest wskazane, aby używać LOOPSjak to pogorszyć wydajność

Odpowiedział 24/01/2013 o 07:43
źródło użytkownik

głosy
1

Wiem, że późno, ale ostatnio miałem ten wymóg i wpadł na poniższym kodzie. Nie mam wyboru użytkownika do korzystania z określonej funkcji. Mam nadzieję że to pomoże.

SELECT 
    SUBSTRING(
                SUBSTRING('Hello John Smith' ,0,CHARINDEX(' ','Hello John Smith',CHARINDEX(' ','Hello John Smith')+1)
                        ),CHARINDEX(' ','Hello John Smith'),LEN('Hello John Smith')
            )
Odpowiedział 17/09/2018 o 21:07
źródło użytkownik

głosy
1

Proste rozwiązanie do parsowania imię i nazwisko

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 (iw wielu innych wydaje ...) Mam listę imion i nazwisk, oddzielonych pojedynczym odstępem. To może być stosowany bezpośrednio wewnątrz select do analizowania imię i nazwisko.

-- 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
Odpowiedział 20/08/2018 o 18:59
źródło użytkownik

głosy
1

Oto funkcja, która będzie osiągnąć cel Pytanie dotyczącą podziału ciąg i dostępu 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

Stosowanie:

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

Wynik:

John
Odpowiedział 26/04/2018 o 21:16
źródło użytkownik

głosy
1

Odpowiedź Aaron Bertranda jest wielki, ale wadliwa. Nie jest dokładnie sobie przestrzeń jako ogranicznik (jak w przykładzie na oryginalnym mowa) od pasków funkcji długości wzdłużnych przestrzeni.

Poniżej znajduje jego kod, z niewielką regulację, aby umożliwić separatora przestrzeni:

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
    );
Odpowiedział 22/03/2018 o 14:38
źródło użytkownik

głosy
1

Począwszy od SQL Server 2016 mamy string_split

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

SELECT value FROM string_split(@string, ',')
Odpowiedział 04/09/2017 o 21:52
źródło użytkownik

głosy
1

Czysta rozwiązanie oparte stosując zestaw TVFz rekurencyjny CTE. Można JOINi APPLYto funkcja do każdego zestawu 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

Stosowanie:

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

Wynik:

value   index
-------------
John    1
Odpowiedział 13/01/2015 o 06:37
źródło użytkownik

głosy
0

Można użyć STRING_SPLITfunkcji dostępnej w SQL Server 2016 lub nowszym. Należy pamiętać, że nie ma gwarancji, że podciągi zostaną zwrócone w określonej kolejności.

WITH testdata(id, string) AS (
    SELECT 1, NULL UNION ALL
    SELECT 2, 'a' UNION ALL
    SELECT 3, 'a b' UNION ALL
    SELECT 4, 'a b c' UNION ALL
    SELECT 5, 'a b c d'
)
SELECT testdata.id, testdata.string, (
    SELECT value AS substr FROM STRING_SPLIT(testdata.string, ' ') FOR XML PATH(''), TYPE
).value('substr[2]', 'VARCHAR(100)') AS [2nd_substr]
FROM testdata

Gdzie:

  • STRING_SPLIT zwraca tabelę z jednej kolumnie o nazwie value
  • FOR XML PATH('') przekształca wiersze do <substr>a</substr><substr>b</substr>...
  • TYPEprzekształca wyżej XMLdataType
  • value('substr[2]', 'VARCHAR(100)')biegnie wyrażenie XPath na powyższe i zwraca VARCHARtyp danych

Wynik:

| id | string  | 2nd_substr |
|----|---------|------------|
| 1  | NULL    | NULL       |
| 2  | a       | NULL       |
| 3  | a b     | b          |
| 4  | a b c   | b          |
| 5  | a b c d | b          |
Odpowiedział 24/01/2018 o 12:27
źródło użytkownik

głosy
0

Nowoczesne podejście używając STRING_SPLIT wymaga SQL Server 2016 i powyżej.

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 jest to możliwe, aby uzyskać th n pierwiastka z liczby wierszy.

Odpowiedział 02/01/2018 o 15:02
źródło użytkownik

głosy
0

budynek na @NothingsImpossible roztworze lub, raczej, wypowiedzieć się na temat najbardziej głosowało odpowiedź (tuż poniżej przyjętego jednego), znalazłem następujące szybkiego i brzydka rozwiązaniem spełnienia własnych potrzeb - ma zaletę, że są wyłącznie w domenie SQL.

dany ciąg „pierwsze, po drugie, po trzecie, czwarte, piąte”, powiedzmy, chcę dostać trzeci token. to działa tylko wtedy, gdy wiemy, jak wiele tokeny ciąg będzie miał - w tym przypadku jest to 5. więc mój sposób działania jest posiekać dwa ostatnie znaki z dala (zapytanie wewnętrzna), a następnie posiekać dwa pierwsze znaki z dala ( zapytanie zewnętrzna)

Wiem, że to jest brzydkie i obejmuje specyficzne warunki byłem, ale zamieszczam go na wszelki wypadek ktoś uzna to za pożyteczne. Twoje zdrowie

select 
    REVERSE(
        SUBSTRING(
            reverse_substring, 
            0, 
            CHARINDEX(';', reverse_substring)
        )
    ) 
from 
(
    select 
        msg,
        SUBSTRING(
            REVERSE(msg), 
            CHARINDEX(
                ';', 
                REVERSE(msg), 
                CHARINDEX(
                    ';',
                    REVERSE(msg)
                )+1
            )+1,
            1000
        ) reverse_substring
    from 
    (
        select 'first;second;third;fourth;fifth' msg
    ) a
) b
Odpowiedział 31/10/2016 o 14:18
źródło użytkownik

głosy
0
declare @strng varchar(max)='hello john smith'
select (
    substring(
        @strng,
        charindex(' ', @strng) + 1,
        (
          (charindex(' ', @strng, charindex(' ', @strng) + 1))
          - charindex(' ',@strng)
        )
    ))
Odpowiedział 14/07/2016 o 05:29
źródło użytkownik

głosy
0

I devoloped to,

declare @x nvarchar(Max) = 'ali.veli.deli.';
declare @item nvarchar(Max);
declare @splitter char='.';

while CHARINDEX(@splitter,@x) != 0
begin
    set @item = LEFT(@x,CHARINDEX(@splitter,@x))
    set @x    = RIGHT(@x,len(@x)-len(@item) )
     select @item as item, @x as x;
end

Tylko uwaga zalecana jest kropka „” że koniec @x zawsze powinno tam być.

Odpowiedział 15/10/2015 o 10:50
źródło użytkownik

głosy
0

jeśli ktoś chce dostać się tylko jedna część seperatured tekstu można użyć tego

select * from fromSplitStringSep ( 'word1 wordr2 słowo3',”„)

CREATE function [dbo].[SplitStringSep] 
(
    @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
  )
Odpowiedział 13/02/2015 o 09:14
źródło użytkownik

głosy
0
CREATE FUNCTION [dbo].[fnSplitString] 
( 
    @string NVARCHAR(MAX), 
    @delimiter CHAR(1) 
) 
RETURNS @output TABLE(splitdata NVARCHAR(MAX) 
) 
BEGIN 
    DECLARE @start INT, @end INT 
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string) 
    WHILE @start < LEN(@string) + 1 BEGIN 
        IF @end = 0  
            SET @end = LEN(@string) + 1

        INSERT IGNORE  INTO @output (splitdata)  
        VALUES(SUBSTRING(@string, @start, @end - @start)) 
        SET @start = @end + 1 
        SET @end = CHARINDEX(@delimiter, @string, @start)

    END 
    RETURN 
END

I używać go

select *from dbo.fnSplitString('Querying SQL Server','')
Odpowiedział 20/12/2014 o 11:58
źródło użytkownik

głosy
0

podczas gdy podobna do odpowiedzi XML opartej przez josejuan, stwierdziliśmy, że przetwarzanie ścieżkę xml tylko raz, a następnie odchylając była umiarkowanie bardziej efektywne:

select ID,
    [3] as PathProvidingID,
    [4] as PathProvider,
    [5] as ComponentProvidingID,
    [6] as ComponentProviding,
    [7] as InputRecievingID,
    [8] as InputRecieving,
    [9] as RowsPassed,
    [10] as InputRecieving2
    from
    (
    select id,message,d.* from sysssislog cross apply       ( 
          SELECT Item = y.i.value('(./text())[1]', 'varchar(200)'),
              row_number() over(order by y.i) as rn
          FROM 
          ( 
             SELECT x = CONVERT(XML, '<i>' + REPLACE(Message, ':', '</i><i>') + '</i>').query('.')
          ) AS a CROSS APPLY x.nodes('i') AS y(i)
       ) d
       WHERE event
       = 
       'OnPipelineRowsSent'
    ) as tokens 
    pivot 
    ( max(item) for [rn] in ([3],[4],[5],[6],[7],[8],[9],[10]) 
    ) as data

pobiegł w 8:30

select id,
tokens.value('(/n[3])', 'varchar(100)')as PathProvidingID,
tokens.value('(/n[4])', 'varchar(100)') as PathProvider,
tokens.value('(/n[5])', 'varchar(100)') as ComponentProvidingID,
tokens.value('(/n[6])', 'varchar(100)') as ComponentProviding,
tokens.value('(/n[7])', 'varchar(100)') as InputRecievingID,
tokens.value('(/n[8])', 'varchar(100)') as InputRecieving,
tokens.value('(/n[9])', 'varchar(100)') as RowsPassed
 from
(
    select id, Convert(xml,'<n>'+Replace(message,'.','</n><n>')+'</n>') tokens
         from sysssislog 
       WHERE event
       = 
       'OnPipelineRowsSent'
    ) as data

pobiegł w 9:20

Odpowiedział 08/12/2014 o 03:59
źródło użytkownik

głosy
0

Rozwiązanie rekurencyjne CTE z bólu serwera przetestować

MS SQL Server 2008 Schemat konfiguracji :

create table Course( Courses varchar(100) );
insert into Course values ('Hello John Smith');

Zapytanie 1 :

with cte as
   ( select 
        left( Courses, charindex( ' ' , Courses) ) as a_l,
        cast( substring( Courses, 
                         charindex( ' ' , Courses) + 1 , 
                         len(Courses ) ) + ' ' 
              as varchar(100) )  as a_r,
        Courses as a,
        0 as n
     from Course t
    union all
      select 
        left(a_r, charindex( ' ' , a_r) ) as a_l,
        substring( a_r, charindex( ' ' , a_r) + 1 , len(a_R ) ) as a_r,
        cte.a,
        cte.n + 1 as n
    from Course t inner join cte 
         on t.Courses = cte.a and len( a_r ) > 0

   )
select a_l, n from cte
--where N = 1

wyniki :

|    A_L | N |
|--------|---|
| Hello  | 0 |
|  John  | 1 |
| Smith  | 2 |
Odpowiedział 16/01/2014 o 11:38
źródło użytkownik

głosy
0

To jest coś zrobiłem, aby uzyskać konkretny wyraz w ciąg. (Testowane w MSSQL 2008)

Po pierwsze, tworząc następujące funkcje: (znaleziono 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żna go używać tak:

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

które zwracają 1111

Odpowiedział 25/07/2013 o 12:07
źródło użytkownik

głosy
0

Cóż, moje nie jest wszystko, co prostsze, ale tutaj jest kod używam podzielić zmienną wejściową oddzielonych przecinkami na poszczególne wartości, i umieścić go w zmiennej tabeli. Jestem pewien, że można zmodyfikować to lekko dzielone na podstawie miejsca, a następnie wykonać 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 IGNORE  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. Jedna inna alternatywa jest wykorzystanie zgodność .NET w SQL Server 2005 sama. Można w zasadzie napisać sobie prosty sposób w .NET, które podzielić ciąg, a następnie odsłonić że jako przechowywanej procedury / funkcji.

Odpowiedział 05/08/2008 o 19:36
źródło użytkownik

głosy
-1

Używam odpowiedź vzczc Korzystanie rekurencyjne CTE za jakiś czas, ale nie chciał, aby go zaktualizować do obsługi zmiennej długości separatora, a także do obsługi ciągów z wiodących i tyle „przekładki”, takie jak kiedy masz plik CSV z rejestrów, takich jak :

"Bob", "Smith", "Sunnyvale", "Kalifornia"

lub gdy masz do czynienia z sześciu części fqn tak jak to pokazano poniżej. Używam tych szeroko do logowania na subject_fqn do audytu, obsługa błędów, itp i parsename obsługuje tylko cztery części:

[netbios_name].[machine_name].[instance].[database].[schema].[table].[column]

Oto moja wersja zaktualizowana, a dzięki vzczc dla jego oryginalnego postu!

select * from [utility].[split_string](N'"this"."string"."gets"."split"."and"."removes"."leading"."and"."trailing"."quotes"', N'"."', N'"', N'"');

select * from [utility].[split_string](N'"this"."string"."gets"."split"."but"."leaves"."leading"."and"."trailing"."quotes"', N'"."', null, null);

select * from [utility].[split_string](N'[netbios_name].[machine_name].[instance].[database].[schema].[table].[column]', N'].[', N'[', N']');

create function [utility].[split_string] ( 
  @input       [nvarchar](max) 
  , @separator [sysname] 
  , @lead      [sysname] 
  , @lag       [sysname]) 
returns @node_list table ( 
  [index]  [int] 
  , [node] [nvarchar](max)) 
  begin 
      declare @separator_length [int]= len(@separator) 
              , @lead_length    [int] = isnull(len(@lead), 0) 
              , @lag_length     [int] = isnull(len(@lag), 0); 
      -- 
      set @input = right(@input, len(@input) - @lead_length); 
      set @input = left(@input, len(@input) - @lag_length); 
      -- 
      with [splitter]([index], [starting_position], [start_location]) 
           as (select cast(@separator_length as [bigint]) 
                      , cast(1 as [bigint]) 
                      , charindex(@separator, @input) 
               union all 
               select [index] + 1 
                      , [start_location] + @separator_length 
                      , charindex(@separator, @input, [start_location] + @separator_length) 
               from   [splitter] 
               where  [start_location] > 0) 
      -- 
      insert into @node_list 
                  ([index],[node]) 
        select [index] - @separator_length                   as [index] 
               , substring(@input, [starting_position], case 
                                                            when [start_location] > 0 
                                                                then 
                                                              [start_location] - [starting_position] 
                                                            else 
                                                              len(@input) 
                                                        end) as [node] 
        from   [splitter]; 
      -- 
      return; 
  end; 
go 
Odpowiedział 19/08/2014 o 20:45
źródło użytkownik

głosy
-1

Prosty zoptymalizowany algorytm:

ALTER FUNCTION [dbo].[Split]( @Text NVARCHAR(200),@Splitor CHAR(1) )
RETURNS @Result TABLE ( value NVARCHAR(50)) 
AS
BEGIN
    DECLARE @PathInd INT
    Set @Text+=@Splitor
    WHILE LEN(@Text) > 0
    BEGIN
        SET @PathInd=PATINDEX('%'+@Splitor+'%',@Text)
        INSERT IGNORE  INTO  @Result VALUES(SUBSTRING(@Text, 0, @PathInd))
        SET @Text= SUBSTRING(@Text, @PathInd+1, LEN(@Text))
    END
        RETURN 
END
Odpowiedział 01/05/2014 o 07:26
źródło użytkownik

głosy
-1

Oto UDF SQL, które można podzielić ciąg i pobrać tylko pewien fragment.

create FUNCTION [dbo].[udf_SplitParseOut]
(
    @List nvarchar(MAX),
    @SplitOn nvarchar(5),
    @GetIndex smallint
)  
returns varchar(1000)
AS  

BEGIN

DECLARE @RtnValue table 
(

    Id int identity(0,1),
    Value nvarchar(MAX)
) 


    DECLARE @result varchar(1000)

    While (Charindex(@SplitOn,@List)>0)
    Begin
        Insert Into @RtnValue (value)
        Select Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))
        Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))
    End

    Insert Into @RtnValue (Value)
    Select Value = ltrim(rtrim(@List))

    select @result = value from @RtnValue where ID = @GetIndex

    Return @result
END
Odpowiedział 20/03/2014 o 15:41
źródło użytkownik

głosy
-1

Oto moje rozwiązanie, które mogą pomóc komuś. Modyfikacja odpowiedzi Jonesinator jest powyżej.

Jeśli mam ciąg ustalonych wartościach INT i chcesz tabelę INT zwrócony (Co mogę przyłączyć się). np „1,20,3,343,44,6,8765”

Tworzenie UDF:

IF OBJECT_ID(N'dbo.ufn_GetIntTableFromDelimitedList', N'TF') IS NOT NULL
    DROP FUNCTION dbo.[ufn_GetIntTableFromDelimitedList];
GO

CREATE FUNCTION dbo.[ufn_GetIntTableFromDelimitedList](@String NVARCHAR(MAX),                 @Delimiter CHAR(1))

RETURNS @table TABLE 
(
    Value INT NOT NULL
)
AS 
BEGIN
DECLARE @Pattern NVARCHAR(3)
SET @Pattern = '%' + @Delimiter + '%'
DECLARE @Value NVARCHAR(MAX)

WHILE LEN(@String) > 0
    BEGIN
        IF PATINDEX(@Pattern, @String) > 0
        BEGIN
            SET @Value = SUBSTRING(@String, 0, PATINDEX(@Pattern, @String))
            INSERT IGNORE  INTO @table (Value) VALUES (@Value)

            SET @String = SUBSTRING(@String, LEN(@Value + @Delimiter) + 1, LEN(@String))
        END
        ELSE
        BEGIN
            -- Just the one value.
            INSERT IGNORE  INTO @table (Value) VALUES (@String)
            RETURN
        END
    END

RETURN
END
GO

Następnie uzyskać wyniki w tabeli:

SELECT * FROM dbo.[ufn_GetIntTableFromDelimitedList]('1,20,3,343,44,6,8765', ',')

1
20
3
343
44
6
8765

Aw sprzężenia oświadczenie:

SELECT [ID], [FirstName]
FROM [User] u
JOIN dbo.[ufn_GetIntTableFromDelimitedList]('1,20,3,343,44,6,8765', ',') t ON u.[ID] = t.[Value]

1    Elvis
20   Karen
3    David
343  Simon
44   Raj
6    Mike
8765 Richard

Jeśli chcesz, aby powrócić do listy NVARCHARs zamiast INT potem po prostu zmienić definicję tabeli:

RETURNS @table TABLE 
(
    Value NVARCHAR(MAX) NOT NULL
)
Odpowiedział 20/06/2013 o 00:42
źródło użytkownik

Cookies help us deliver our services. By using our services, you agree to our use of cookies. Learn more