STRING_SPLIT (Transact-SQL)

  • Artikel

Gilt für: SQL Server 2016 (13.x) und höherAzure SQL-DatenbankAzure SQL Managed InstanceAzure Synapse AnalyticsSQL-Endpunkt in Microsoft FabricWarehouse in Microsoft Fabric

Eine Tabellenwertfunktion, die eine Zeichenfolge basierend auf einem angegebenen Trennzeichen in Zeilen mit Teilzeichenfolgen unterteilt.

Kompatibilitätsgrad 130

Für STRING_SPLIT ist mindestens der Kompatibilitätsgrad 130 erforderlich. Bei einem Grad unter 130 kann SQL Server die STRING_SPLIT-Funktion nicht finden.

Unter Anzeigen oder Ändern des Kompatibilitätsgrads einer Datenbank finden Sie Informationen zum Ändern des Datenbank-Kompatibilitätsgrads.

Hinweis

Die Kompatibilitätskonfiguration ist für STRING_SPLIT in Azure Synapse Analytics nicht erforderlich.

Transact-SQL-Syntaxkonventionen

Syntax

STRING_SPLIT ( string , separator [ , enable_ordinal ] )

Argumente

string

Ist ein Ausdruck eines beliebigen Zeichentyps (z.B. nvarchar, varchar, nchar oder char).

Trennzeichen

Ein Ausdruck mit einem einzelnen Zeichen jedes beliebigen Zeichentyps (z.B. nvarchar(1) , varchar(1) , nchar(1) oder char(1) ), der als Trennzeichen für verkettete Teilzeichenfolgen verwendet wird.

enable_ordinal

Ein int- oder bit-Ausdruck, der als Flag zum Aktivieren oder Deaktivieren der Ausgabespalte ordinal dient. Der Wert 1 aktiviert die Spalte ordinal. Wenn enable_ordinal ausgelassen wurde oder den Wert NULL oder 0 hat, ist die Spalte ordinal deaktiviert.

Hinweis

Das Argument enable_ordinal und die Ausgabespalte ordinal werden derzeit in Azure SQL-Datenbank, Azure SQL Managed Instance und Azure Synapse Analytics unterstützt (nur serverlose SQL-Pools). Ab SQL Server 2022 (16.x) sind das Argument und die Ausgabespalte in SQL Server verfügbar.

Rückgabetypen

Wenn die Ausgabespalte ordinal nicht aktiviert ist, gibt STRING_SPLIT eine einspaltige Tabelle zurück, deren Zeilen die Teilzeichenfolgen sind. Der Name der Spalte lautet value. Gibt nvarchar zurück, wenn eines der Eingabeargumente entweder nvarchar oder nchar ist. Andernfalls wird varchar zurückgegeben. Die Länge des Rückgabetyps unterscheidet sich nicht von der Länge des string-Arguments.

Wenn dem Argument enable_ordinal der Wert 1 übergeben wird, wird eine zweite Spalte mit dem Namen ordinal zurückgegeben, die aus den 1-basierten Indexwerten der Position der einzelnen Teilzeichenfolgen in der Eingabezeichenfolge besteht. Der Rückgabetyp ist bigint.

Bemerkungen

STRING_SPLIT gibt eine Zeichenfolge mit getrennten Teilzeichenfolgen sowie ein Zeichen ein, das als Trennzeichen oder Trennlinie verwendet wird. Optional unterstützt die Funktion ein drittes Argument mit dem Wert 0 oder 1, das die Ausgabespalte ordinal deaktiviert bzw. aktiviert.

STRING_SPLIT gibt eine ein- oder zweispaltige Tabelle aus, je nach dem Argument enable_ordinal.

  • Wenn enable_ordinal ausgelassen wurde oder den Wert NULL oder 0 hat, gibt STRING_SPLIT eine einspaltige Tabelle zurück, deren Zeilen die Teilzeichenfolgen enthalten. Der Name der Ausgabespalte lautet value.

  • Wenn enable_ordinal den Wert 1 hat, gibt die Funktion eine zweispaltige Tabelle zurück, einschließlich der Spalte ordinal, die aus den 1-basierten Indexwerten der Teilzeichenfolgen in der ursprünglichen Eingabezeichenfolge besteht.

Beachten Sie, dass das Argument enable_ordinal ein konstanter Wert sein muss und keine Spalte oder Variable sein darf. Der Datentyp muss bit oder int mit dem Wert 0 oder 1 sein. Andernfalls gibt die Funktion einen Fehler aus.

Die Ausgabezeilen können in beliebiger Reihenfolge sein. Die Reihenfolge ist nicht stimmt nicht garantiert mit der Reihenfolge der Teilzeichenfolgen in der Eingabezeichenfolge überein. Sie können die endgültige Sortierreihenfolge überschreiben, indem Sie in der SELECT-Anweisung eine ORDER BY-Klausel verwenden, z. B. ORDER BY value oder ORDER BY ordinal.

0x0000 (char(0) ) ist ein nicht definiertes Zeichen in Windows-Sortierungen und kann nicht in SPRING_SPLIT enthalten sein.

Leere Teilzeichenfolgen der Länge null sind vorhanden, wenn die Eingabezeichenfolge zwei oder mehr aufeinanderfolgende Vorkommen des Trennzeichens enthält. Leere Teilzeichenfolgen werden genauso behandelt wie normale Teilzeichenfolgen. Sie können alle Zeilen, die eine leere Teilzeichenfolge enthalten, mit der WHERE-Klausel herausfiltern, z. B. WHERE value <> ''. Wenn die Eingabezeichenfolge NULL ist, gibt die Tabellenwertfunktion STRING_SPLIT eine leere Tabelle zurück.

Beispielsweise wird in der folgenden SELECT-Anweisung das Leerzeichen als Trennzeichen verwendet:

SELECT value FROM STRING_SPLIT('Lorem ipsum dolor sit amet.', ' ');

Bei einer praktischen Ausführung hat die vorstehende SELECT-Anweisung die folgende Ergebnistabelle zurückgegeben:

value
Lorem
ipsum
dolor
sit
amet.

Das folgende Beispiel aktiviert die Spalte ordinal, indem es 1 als optionales drittes Argument übergibt:

SELECT * FROM STRING_SPLIT('Lorem ipsum dolor sit amet.', ' ', 1);

Diese Anweisung gibt dann die folgende Ergebnistabelle zurück:

value ordinal
Lorem 1
ipsum 2
dolor 3
sit 4
amet. 5

Beispiele

A. Teilen einer Zeichenfolge mit durch Trennzeichen getrennten Werten (CSV)

Analysieren einer durch Komma getrennten Liste von Werten und Zurückgeben aller nicht leeren Token:

DECLARE @tags NVARCHAR(400) = 'clothing,road,,touring,bike'  
  
SELECT value  
FROM STRING_SPLIT(@tags, ',')  
WHERE RTRIM(value) <> '';

STRING_SPLIT gibt eine leere Zeichenfolge zurück, wenn zwischen dem Trennzeichen nichts vorhanden ist. Die Bedingung RTRIM(value) <> '' entfernt leere Token.

B. Teilen einer Zeichenfolge mit durch Trennzeichen getrennten Werten in einer Spalte

Die Produkttabelle verfügt über eine Spalte mit einer durch Komma getrennte Liste von Tags, wie in diesem Beispiel dargestellt wird:

ProductId Name Tags
1 Full-Finger Gloves clothing,road,touring,bike
2 LL Headset bike
3 HL Mountain Frame bike,mountain

Die folgende Abfrage wandelt jede Tagliste um und verknüpft sie mit der ursprünglichen Zeile:

SELECT ProductId, Name, value  
FROM Product  
    CROSS APPLY STRING_SPLIT(Tags, ',');

Hier ist das Resultset.

ProductId Name value
1 Full-Finger Gloves clothing
1 Full-Finger Gloves Straße
1 Full-Finger Gloves touring
1 Full-Finger Gloves bike
2 LL Headset bike
3 HL Mountain Frame bike
3 HL Mountain Frame mountain

Hinweis

Die Reihenfolge der Ausgabe kann abweichen, da diese nicht zwangsläufig mit der Reihenfolge der Teilzeichenfolgen in der Eingabezeichenfolge übereinstimmt.

C. Aggregation nach Werten

Benutzer müssen einen Bericht erstellen, der die Anzahl der Produkte pro Tag anzeigt, die nach der Anzahl der Produkte geordnet ist, und sie dürfen nur die Tags mit mehr als zwei Produkten filtern.

SELECT value as tag, COUNT(*) AS [number_of_articles]  
FROM Product  
    CROSS APPLY STRING_SPLIT(Tags, ',')  
GROUP BY value  
HAVING COUNT(*) > 2  
ORDER BY COUNT(*) DESC;

D: Nach Tagwert suchen

Entwickler müssen Abfragen erstellen, die Artikel nach Schlüsselwörtern finden. Sie können folgende Abfragen verwenden:

Um Produkte mit einem einzelnen Tag zu finden (clothing):

SELECT ProductId, Name, Tags  
FROM Product  
WHERE 'clothing' IN (SELECT value FROM STRING_SPLIT(Tags, ','));

Produkte mit zwei angegebenen Tags finden (clothing und road):

SELECT ProductId, Name, Tags  
FROM Product  
WHERE EXISTS (SELECT *  
    FROM STRING_SPLIT(Tags, ',')  
    WHERE value IN ('clothing', 'road'));

E. Suchen nach Zeilen nach einer Liste von Werten

Entwickler müssen eine Abfrage erstellen, die Artikel nach einer Liste von IDs sucht. Sie können die folgende Abfrage verwenden:

SELECT ProductId, Name, Tags  
FROM Product  
JOIN STRING_SPLIT('1,2,3',',')
    ON value = ProductId;

Die vorhergehende Verwendung von STRING_SPLIT ist ein Ersatz für ein gängiges Antimuster. Ein solches Antimuster kann die Erstellung einer dynamischen SQL-Zeichenfolge auf der Anwendungsschicht oder in Transact-SQL mit einbeziehen. Ein Antimuster kann auch mit dem LIKE-Operator erreicht werden. Siehe das folgende Beispiel einer SELECT-Anweisung:

SELECT ProductId, Name, Tags  
FROM Product  
WHERE ',1,2,3,' LIKE '%,' + CAST(ProductId AS VARCHAR(20)) + ',%';

F. Suchen nach Zeilen anhand von Ordinalwerten

Die folgende Anweisung sucht alle Zeilen mit einem geraden Indexwert:

SELECT *
FROM STRING_SPLIT('Austin,Texas,Seattle,Washington,Denver,Colorado', ',', 1)
WHERE ordinal % 2 = 0;

Die obige Anweisung gibt die folgende Tabelle zurück:

value ordinal
Texas 2
Washington 4
Colorado 6

G. Sortieren von Zeilen nach Ordinalwerten

Die folgende Anweisung gibt die Werte der geteilten Teilzeichenfolgen der Eingabezeichenfolge und deren Ordinalwerte zurück, sortiert nach der Spalte ordinal:

SELECT * FROM STRING_SPLIT('E-D-C-B-A', '-', 1) ORDER BY ordinal DESC;

Die obige Anweisung gibt die folgende Tabelle zurück:

value ordinal
A 5
B 4
C 3
D 2
E 1

Nächste Schritte