Wie teile ich einen abgegrenzten String auf, um auf einzelne Elemente zugreifen zu können?

Question

Mit SQL Server, wie kann ich einen String aufteilen, um auf Element x zugreifen zu können?

Nehmen wir einen String "Hallo John Smith". Wie kann ich den String nach Leerzeichen aufteilen und auf das Element an Index 1 zugreifen, das "John" zurückgeben sollte?

Answer 1

Ich glaube nicht, dass SQL Server eine eingebaute Split-Funktion hat, also außer einer UDF ist die einzige andere Antwort, die ich kenne, die PARSENAME-Funktion zu übernehmen:

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

PARSENAME nimmt eine Zeichenkette und teilt sie am Punktzeichen auf. Es nimmt eine Zahl als zweites Argument, und diese Zahl gibt an, welches Segment der Zeichenkette zurückgegeben werden soll (von hinten nach vorne arbeiten).

SELECT PARSENAME(REPLACE('Hello John Smith', ' ', '.'), 3)  --gibt Hello zurück

Das offensichtliche Problem besteht darin, dass die Zeichenkette bereits einen Punkt enthält. Ich denke immer noch, dass die Verwendung einer UDF der beste Weg ist...irgendwelche anderen Vorschläge?

Answer 2

Sie finden möglicherweise die Lösung in _SQL User Defined Function to Parse a Delimited String_ hilfreich (von The Code Project).

Sie können diese einfache Logik verwenden:

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

Answer 3

Zuerst erstellen Sie eine Funktion (mit CTE, Common Table Expression entfällt die Notwendigkeit einer temp-Tabelle)

 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

Dann verwenden Sie es wie eine normale Tabelle (oder passen Sie es an, um in Ihrer vorhandenen gespeicherten Prozedur zu passen) wie folgt.

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

Aktualisierung

Die vorherige Version würde bei einer Eingabezeichenfolge länger als 4000 Zeichen fehlschlagen. Diese Version überwindet diese Einschränkung:

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

Die Verwendung bleibt gleich.

Answer 4

Die meisten Lösungen hier verwenden while-Schleifen oder rekursive CTEs. Ein set-basierter Ansatz wird überlegen sein, versprochen, wenn Sie ein Trennzeichen verwenden können, das nicht ein Leerzeichen ist:

CREATE FUNCTION [dbo].[SplitString]
    (
        @List NVARCHAR(MAX),
        @Delim VARCHAR(255)
    )
    RETURNS TABLE
    AS
        RETURN ( SELECT [Value], idx = RANK() OVER (ORDER BY n) FROM 
          ( 
            SELECT n = Number, 
              [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
        );

Beispielhafte Verwendung:

SELECT Value FROM dbo.SplitString('foo,bar,blat,foo,splunge',',')
  WHERE idx = 3;

Ergebnisse:

----
blat

Sie könnten auch das idx, das Sie möchten, als Argument an die Funktion übergeben, aber das überlasse ich dem Leser als Übung.

Dies ist nicht möglich nur mit der nativen STRING_SPLIT-Funktion, die in SQL Server 2016 hinzugefügt wurde, da nicht garantiert ist, dass die Ausgabe in der Reihenfolge der ursprünglichen Liste angezeigt wird. Mit anderen Worten, wenn Sie 3,6,1 übergeben, wird das Ergebnis wahrscheinlich in dieser Reihenfolge sein, aber es könnte auch 1,3,6 sein. Ich habe um Hilfe aus der Community gebeten, um die eingebaute Funktion hier zu verbessern:

• Bitte helfen Sie bei STRING_SPLIT-Verbesserungen

Mit genügend qualitativem Feedback werden sie vielleicht tatsächlich einige dieser Verbesserungen in Betracht ziehen:

• STRING_SPLIT ist nicht vollständig

Mehr über Split-Funktionen, warum (und Beweis dafür), dass while-Schleifen und rekursive CTEs nicht skalieren, und bessere Alternativen, wenn Strings aus der Anwendungsebene kommen:

• Strings auf die richtige Art teilen - oder die nächstbeste Art
• Splitting Strings: Eine Nachbetrachtung
• Strings aufteilen: Jetzt mit weniger T-SQL
• Vergleich von String-Splitting- / Verkettungsmethoden
• Verarbeitung einer Liste von Ganzzahlen: mein Ansatz
• Aufteilen einer Liste von Ganzzahlen: eine weitere Zusammenfassung
• Mehr zum Aufteilen von Listen: benutzerdefinierte Trennzeichen, Duplikate verhindern und Reihenfolge beibehalten
• Duplikate aus Strings in SQL Server entfernen

Auf SQL Server 2016 oder höher sollten Sie jedoch STRING_SPLIT() und STRING_AGG() verwenden:

• Leistungssurprises und Annahmen: STRING_SPLIT()
• STRING_SPLIT() in SQL Server 2016: Nachbetrachtung #1
• STRING_SPLIT() in SQL Server 2016: Nachbetrachtung #2
• SQL Server v.Next: STRING_AGG() Leistung
• Lösen Sie alte Probleme mit den neuen STRING_AGG- und STRING_SPLIT-Funktionen von SQL Server

Answer 5

Diese Frage dreht sich nicht um einen String-Split-Ansatz, sondern darum, wie man das n-te Element erhält.

Die meisten Antworten hier verwenden irgendeine Form von String-Splitting mit Rekursion, CTEs, mehrere CHARINDEX, REVERSE und PATINDEX, erfinden Funktionen, rufen CLR-Methoden auf, verwenden Nummertabellen, CROSS APPLY... Die meisten Antworten bestehen aus vielen Codezeilen.

Aber - wenn du wirklich nichts weiter als einen Ansatz benötigst, um das n-te Element zu erhalten - kann dies als echter Einzeiler gemacht werden, keine UDF, nicht einmal ein Sub-Select... Und als zusätzlicher Vorteil: typsicher

Hole Teil 2, der durch ein Leerzeichen begrenzt ist:

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

Natürlich kannst du Variablen verwenden für das Trennzeichen und die Position (verwende sql:column, um die Position direkt aus dem Wert einer Abfrage abzurufen):

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

Wenn dein String möglicherweise verbotene Zeichen enthält (insbesondere eines unter &><), kannst du es trotzdem auf diese Weise tun. Verwende einfach zuerst FOR XML PATH auf deinem String, um alle verbotenen Zeichen implizit durch die passende Escape-Sequenz zu ersetzen.

Es ist ein sehr spezieller Fall, wenn - zusätzlich - dein Trennzeichen das Semikolon ist. In diesem Fall ersetze ich das Trennzeichen zuerst durch '#DLMT#' und ersetze dies schließlich durch die XML-Tags:

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

UPDATE für SQL-Server 2016+

Bedauerlicherweise haben die Entwickler vergessen, den Index des Teils mit STRING_SPLIT zurückzugeben. Aber, unter Verwendung von SQL-Server 2016+ gibt es JSON_VALUE und OPENJSON.

Mit JSON_VALUE können wir die Position als Index-Array übergeben.

Für OPENJSON besagt die Dokumentation klar:

Wenn OPENJSON ein JSON-Array analysiert, gibt die Funktion die Indizes der Elemente im JSON-Text als Schlüssel zurück.

Ein String wie 1,2,3 braucht nichts weiter als Klammern: [1,2,3].
Ein String von Wörtern wie this is an example muss als ["this","is","an","example"] vorliegen.
Dies sind sehr einfache Zeichenkettenoperationen. Probiere es einfach aus:

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

--Wir können den JSON-Pfad '$[1]' mit CONCAT aufbauen
SELECT JSON_VALUE('["' + REPLACE(@str,' ','","') + '"]',CONCAT('$[',@position-1,']'));

--Siehe dies für einen positions sicheren String-Splitter (nullbasiert):

SELECT  JsonArray.[key] AS [Position]
       ,JsonArray.[value] AS [Part]
FROM OPENJSON('["' + REPLACE(@str,' ','","') + '"]') JsonArray

In diesem Beitrag habe ich verschiedene Ansätze getestet und festgestellt, dass OPENJSON wirklich schnell ist. Sogar viel schneller als die berühmte "delimitedSplit8k()" Methode...

UPDATE 2 - Die Werte typsicher abrufen

Wir können ganz einfach ein Array innerhalb eines Arrays durch die Verwendung von verdoppelten [[]] erstellen. Dies ermöglicht eine getypte WITH-Klausel:

DECLARE  @SomeDelimitedString VARCHAR(100)='part1|1|20190920';

DECLARE @JsonArray NVARCHAR(MAX)=CONCAT('[["',REPLACE(@SomeDelimitedString,'|','","'),'"]]');

SELECT @SomeDelimitedString          AS TheOriginal
      ,@JsonArray                    AS TransformedToJSON
      ,ValuesFromTheArray.*
FROM OPENJSON(@JsonArray)
WITH(TheFirstFragment  VARCHAR(100) '$[0]'
    ,TheSecondFragment INT          '$[1]'
    ,TheThirdFragment  DATE         '$[2]') ValuesFromTheArray