T-SQL: Zeichenketten zerlegen — STRING_SPLIT und die Wege davor

Irgendwann steht in einer Spalte '4711,4712,4713', und die Abfrage soll drei Zeilen liefern statt einer. Seit SQL Server 2016 gibt es dafür STRING_SPLIT, und die meisten Anleitungen im Netz hören damit auf. Sie verschweigen zwei Dinge: dass die Funktion bis 2022 keine Reihenfolge mitlieferte, und dass sie nur ein einzelnes Trennzeichen kennt. Dieser Artikel zeigt, was STRING_SPLIT heute leistet, welche der älteren Verfahren deshalb noch nicht überflüssig sind — und in welchen Fällen die richtige Antwort lautet, gar nicht zu zerlegen.

Zu diesem Thema gibt es Seminare und Beratung; das Angebot richtet sich an Unternehmen, Behörden und Selbstständige.

Die Anfrage ist unverbindlich und kostenlos; Umfang und Konditionen klären wir danach individuell.

Warum das überhaupt vorkommt

Kommagetrennte Werte in einer Spalte entstehen selten aus Überzeugung. Sie kommen aus einer Importdatei, aus einem Mehrfachauswahl-Feld im Frontend, aus einer Schnittstelle, die eine Liste als Text übergibt — oder aus einer Tabelle, die vor fünfzehn Jahren jemand so entworfen hat und die seither in Betrieb ist.

Deshalb gehört an den Anfang eine unbequeme Frage: Muss hier wirklich zerlegt werden, oder ist das Datenmodell die eigentliche Baustelle? Eine Spalte, die mehrere Werte trägt, verletzt die erste Normalform. Sie macht jede Suche zu einem Scan, verhindert Fremdschlüssel, und kein Index hilft. Wer WHERE artikel LIKE '%4712%' schreibt, hat das Problem nicht gelöst, sondern verschoben — und findet nebenbei auch die 14712.

Wenn die Zeichenkette aber von außen kommt und nur für die Dauer einer Abfrage zerlegt werden muss, ist Zerlegen genau richtig. Diese beiden Fälle sauber zu trennen, ist die halbe Antwort.

Wann Zerlegen die richtige Antwort ist

• Parameterlisten aus der Anwendung — eine Suchmaske übergibt ausgewählte Kennungen als eine Zeichenkette
• Importdateien — eine Spalte der Rohdaten trägt mehrere Werte, die beim Laden aufgetrennt werden
• Konfigurationswerte — eine Einstellung hält eine Aufzählung, die nur beim Lesen zerfällt
• Protokoll- und Log-Zeilen — ein Textfeld mit fester Struktur soll für eine Auswertung zerlegt werden
• Migrationen — ein Altbestand mit Listenspalte wird einmalig in eine Zuordnungstabelle überführt
• Ad-hoc-Analysen — eine einmalige Frage, für die sich kein Modellumbau lohnt

Nicht dazu gehört der Dauerbetrieb: Wer bei jeder Abfrage dieselbe Spalte zerlegt, um darin zu suchen, zahlt dafür bei jedem Aufruf. Das ist der Moment, in dem sich eine Zuordnungstabelle rechnet — und das Thema, das im Seminar Relationales Datenbank-Design den Kern bildet.

STRING_SPLIT — was es kann, und was lange fehlte

STRING_SPLIT ist eine Tabellenwertfunktion. Sie nimmt einen Text und ein Trennzeichen und liefert eine Ergebnismenge mit der Spalte value:

SELECT value
  FROM STRING_SPLIT('4711,4712,4713', ',');

Drei Einschränkungen sind wichtig, und genau sie fehlen in den meisten Kurzanleitungen.

Das Trennzeichen ist genau ein Zeichen. Der Parameter ist auf ein Zeichen begrenzt; ', ' mit Komma und Leerzeichen geht nicht. Wer mit mehrstelligen Trennzeichen arbeitet, muss sie vorher ersetzen — oder ein anderes Verfahren wählen.

Die Reihenfolge war bis 2022 nicht abrufbar. Bis SQL Server 2019 gab es keine Positionsangabe. Seit SQL Server 2022 existiert ein dritter Parameter:

SELECT value, ordinal
  FROM STRING_SPLIT('rot,grün,blau', ',', 1)
 ORDER BY ordinal;

Ohne diesen Parameter — und auf jeder älteren Version — ist die Reihenfolge der Ergebniszeilen nicht zugesichert. In der Praxis kommt sie meist passend heraus, und genau das macht den Fehler gefährlich: Er fällt im Test nicht auf und schlägt später unter anderem Ausführungsplan zu. Wer die Position braucht, darf sie nicht aus der Ausgabereihenfolge ableiten.

Die Funktion braucht Kompatibilitätsgrad 130 oder höher. Steht die Datenbank noch auf einem älteren Grad — was nach einer Migration von einer Altversion oft der Fall ist —, meldet SQL Server, dass die Funktion nicht gefunden wurde. Das ist kein Versionsproblem der Instanz, sondern eine Einstellung der Datenbank:

SELECT name, compatibility_level FROM sys.databases WHERE name = DB_NAME();

Leere Abschnitte liefert die Funktion übrigens mit: 'a,,b' ergibt drei Zeilen, die mittlere leer. Wer das nicht will, filtert sie mit WHERE value <> '' weg.

Die Wege davor — und wann sie noch gebraucht werden

OPENJSON — der unterschätzte Weg

OPENJSON gibt es seit derselben Version wie STRING_SPLIT, kann aber zwei Dinge mehr. Es liefert die Position über die Spalte key, und weil die Liste vorher zu JSON umgebaut wird, sind mehrstellige Trennzeichen kein Hindernis:

DECLARE @liste nvarchar(max) = N'rot; grün; blau';

SELECT CAST([key] AS int) + 1 AS position
     , TRIM([value])          AS wert
  FROM OPENJSON('["' + REPLACE(STRING_ESCAPE(@liste, 'json'), '; ', '","') + '"]')
 ORDER BY CAST([key] AS int);

STRING_ESCAPE ist der Teil, den man nicht weglassen darf: Ohne ihn zerlegt ein Anführungszeichen oder ein Backslash im Text das JSON, und die Abfrage scheitert — im besten Fall.

Ebenso wichtig ist die Reihenfolge: erst maskieren, dann das Trennzeichen ersetzen. Umgekehrt würde STRING_ESCAPE die gerade eingefügten JSON-Anführungszeichen gleich wieder mit maskieren, und statt einer Liste entstünde ein einziger langer Wert. Damit ist der Weg auch auf SQL Server 2016 bis 2019 verfügbar, wo STRING_SPLIT noch keine Position kennt.

Zahlentabelle — der klassische, mengenbasierte Weg

Eine Tabelle mit fortlaufenden Zahlen, ein SUBSTRING je Position, fertig. Der Weg funktioniert auf jeder Version, ist mengenbasiert und liefert die Position ohne Zusatzaufwand. Er braucht dafür eine Hilfstabelle im Schema — und genau daran scheitert er in Umgebungen, in denen man nichts anlegen darf.

WITH zahlen AS (
  SELECT TOP (4000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
    FROM sys.all_columns a CROSS JOIN sys.all_columns b
)
SELECT ROW_NUMBER() OVER (ORDER BY n) AS position
     , SUBSTRING(@liste, n, CHARINDEX(',', @liste + ',', n) - n) AS wert
  FROM zahlen
 WHERE n <= LEN(@liste)
   AND SUBSTRING(',' + @liste, n, 1) = ',';

Der Trick steckt in der WHERE-Zeile, und er sieht auf den ersten Blick nach einem Versatzfehler aus: geprüft wird in ',' + @liste, gelesen aber aus @liste. Genau das ist die Absicht. Durch das vorangestellte Trennzeichen liegt an Position n des verschobenen Textes das Zeichen vor Position n des Originals. Die Bedingung trifft also dort zu, wo im Original ein neues Element beginnt — bei '4711,4712,4713' sind das die Positionen 1, 6 und 11.

Deshalb ist n eine Zeichenposition, keine Elementnummer. Wer 1, 2, 3 braucht, nimmt ROW_NUMBER() darüber — n direkt als Position auszugeben, ergäbe 1, 6, 11.

Die abgeleitete Zahlenmenge aus sys.all_columns ist der übliche Notbehelf, wenn keine echte Hilfstabelle existiert. Für Dauerbetrieb ist eine physische Zahlentabelle mit Index die bessere Wahl.

XML — verbreitet, aber mit Stolperstein

Der XML-Weg begegnet einem in fast jedem gewachsenen T-SQL-Bestand:

SELECT x.v.value('.', 'nvarchar(100)') AS wert
  FROM (SELECT CAST('<i>' + REPLACE(@liste, ',', '</i><i>') + '</i>' AS xml) AS d) q
 CROSS APPLY q.d.nodes('/i') AS x(v);

Er funktioniert — bis ein &, ein < oder ein > im Text steht. Dann ist das erzeugte XML ungültig und die Abfrage bricht ab. Wer den Weg behalten will, muss die drei Zeichen vorher ersetzen. In neuem Code gibt es dafür keinen Grund mehr.

Dafür kann derselbe Kniff aber etwas, das STRING_SPLIT bis heute nicht kann: gezielt das n-te Element herausgreifen, ohne vorher alles zu zerlegen. Der XPath-Index macht das in einem Ausdruck:

DECLARE @liste varchar(200) = 'A|B|C';

SELECT CAST('<x>' + REPLACE(@liste, '|', '</x><x>') + '</x>' AS xml)
         .value('/x[2]', 'varchar(100)') AS zweites_element;

⚠ Die Längenangabe im zweiten Argument ist Pflicht. 'varchar' ohne Länge wird in diesem Zusammenhang wie bei CAST und CONVERT als varchar(30) ausgewertet — längere Werte werden stillschweigend abgeschnitten, und der Fehler fällt erst bei den Daten auf, die über dreißig Zeichen gehen.

Genau diese Frage — das n-te Vorkommen in einer Zeichenkette — habe ich im Juli 2015 auf Stack Overflow beantwortet, mit eben diesem XML-Weg. Das Datum erklärt die Wahl: STRING_SPLIT kam erst mit SQL Server 2016, ein Jahr später. Wer heute in einem gewachsenen Bestand auf diese Konstruktion stößt, sieht also keinen Umweg, sondern den damaligen Stand der Technik.

Rekursive CTE und Schleife

Beide gehen, beide sind langsam. Die rekursive CTE arbeitet Zeichen für Zeichen und läuft in die Vorgabegrenze von 100 Rekursionsebenen — bei längeren Listen bricht sie mit einer Fehlermeldung ab. Anheben lässt sie sich mit OPTION (MAXRECURSION n), wobei 0 „unbegrenzt" bedeutet; damit fällt allerdings auch der Schutz vor einer nie endenden Rekursion weg. Die Schleife mit WHILE und CHARINDEX ist der Klassiker aus der Anwendungsprogrammierung — und in einer Datenbank fast immer die schlechteste Wahl, weil sie zeilenweise denkt, wo die Datenbank mengenbasiert rechnet. Dieselbe Denkfalle steckt hinter den meisten langsamen Triggern, wie im Artikel zu SQL Server Audit-Triggern beschrieben.

Nur das n-te Element holen

Oft ist gar nicht die ganze Liste gefragt, sondern genau ein Stück daraus — der Dateiname zwischen dem zweiten und dritten Unterstrich, das dritte Feld einer Protokollzeile. Alle Verfahren können das, aber jedes zählt anders, und genau daran gehen die meisten Versuche schief.

Die gemischte Zählweise ist die häufigste Fehlerquelle: XPath und ordinal beginnen bei 1, JSON bei 0. Wer zwischen den Verfahren wechselt, greift ohne Anpassung um eins daneben — und weil das Ergebnis trotzdem plausibel aussieht, fällt es oft erst beim Kunden auf.

Der kürzeste Weg führt über JSON_VALUE, wenn nur ein einzelner Wert gebraucht wird:

SELECT JSON_VALUE('["A","B","C"]', '$[1]') AS zweites_element;   -- B

Für eine Liste mit eigenem Trennzeichen gilt derselbe Umbau wie oben bei OPENJSON — erst STRING_ESCAPE, dann das Trennzeichen ersetzen. ⚠ JSON_VALUE liefert nvarchar(4000); längere Werte kommen als NULL zurück, nicht abgeschnitten. Wer damit rechnen muss, nimmt OPENJSON und liest die Spalte value.

Beim XML-Weg gibt es zwei Zugriffe, die leicht verwechselt werden:

DECLARE @x xml = '<x>A</x><x>B</x><x>C</x>';

SELECT @x.query('/x[2]')                    AS als_element   -- <x>B</x>
     , @x.value('/x[2]', 'varchar(100)')    AS als_wert;     -- B

query() gibt das Element samt Tags zurück, value() nur den Inhalt. Wer sich wundert, warum überall spitze Klammern im Ergebnis stehen, hat query() erwischt.

Bei STRING_SPLIT vor SQL Server 2022 gibt es diesen Weg schlicht nicht — die Ausgabereihenfolge ist ohne ordinal nicht zugesichert, und TOP 1 … OFFSET darauf anzuwenden liefert im Zweifel das falsche Stück.

STRING_AGG — die Gegenrichtung

Manchmal ist die Aufgabe umgekehrt: aus mehreren Zeilen soll eine Zeichenkette werden. Dafür gibt es seit SQL Server 2017 STRING_AGG:

SELECT kunde_id
     , STRING_AGG(artikel_nr, ', ') WITHIN GROUP (ORDER BY artikel_nr) AS artikel
  FROM bestellposition
 GROUP BY kunde_id;

WITHIN GROUP (ORDER BY …) ist hier der Teil, der zählt — ohne ihn ist die Reihenfolge im Ergebnis genauso wenig zugesichert wie bei STRING_SPLIT ohne ordinal. Vor 2017 übernahm diese Aufgabe der bekannte FOR XML PATH-Umweg, der dieselben Escaping-Probleme mitbringt wie der XML-Weg beim Zerlegen.

Ein Hinweis, der öfter übersehen wird: STRING_AGG gehört in die Darstellungsschicht, nicht in die Speicherung. Eine so erzeugte Zeichenkette gehört in einen Bericht oder einen Export — nicht zurück in eine Spalte. Sonst entsteht genau das Modell, das Abbildung 2 links zeigt.

Was das für die Praxis heißt

Auf SQL Server 2022 und neuer: STRING_SPLIT mit enable_ordinal, wenn die Position gebraucht wird. Sonst ohne.

Auf SQL Server 2016 bis 2019: STRING_SPLIT, solange die Reihenfolge egal ist. Sobald die Position zählt, OPENJSON — nicht die Ausgabereihenfolge von STRING_SPLIT interpretieren.

Bei mehrstelligen Trennzeichen: vorher ersetzen, oder OPENJSON.

Auf älteren Versionen oder bei gesetztem Kompatibilitätsgrad unter 130: Zahlentabelle. Der XML-Weg nur, wenn er ohnehin schon im Bestand steht und die Daten nachweislich keine Sonderzeichen führen.

Im Dauerbetrieb mit Suche über die Werte: keines davon. Dann gehört die Liste in eine eigene Tabelle, und die Abfrage wird zu einem gewöhnlichen Join. Was das für Ausführungspläne und Indexnutzung bedeutet, ist das Thema des Seminars Datenbankübergreifendes Performance-Tuning — dort systemneutral, weil dieselbe Frage in PostgreSQL und Oracle genauso auftritt. Wie andere Systeme sie beantworten, zeigt unser Systemvergleich; PostgreSQL bringt mit string_to_table und unnest eigene Antworten mit.

Häufige Fragen (FAQ)

Warum findet SQL Server STRING_SPLIT nicht?

Fast immer liegt es am Kompatibilitätsgrad der Datenbank, nicht an der Version der Instanz. Die Funktion setzt Grad 130 oder höher voraus. Nach einer Migration von einer Altversion bleibt der Grad oft auf dem alten Stand stehen. SELECT compatibility_level FROM sys.databases WHERE name = DB_NAME() zeigt den Ist-Zustand.

Wie bekomme ich die Position der Teilstücke?

Ab SQL Server 2022 über den dritten Parameter: STRING_SPLIT(@text, ',', 1) liefert zusätzlich die Spalte ordinal. Auf älteren Versionen führt OPENJSON zum Ziel, dessen Spalte key die Position enthält. Was man nicht tun darf: die Ausgabereihenfolge von STRING_SPLIT als Position deuten — sie ist ohne ordinal nicht zugesichert.

Kann STRING_SPLIT mit einem mehrstelligen Trennzeichen umgehen?

Nein. Der Parameter ist auf ein einzelnes Zeichen begrenzt. Wer etwa an '; ' trennen will, ersetzt die Folge vorher durch ein einzelnes, im Text nicht vorkommendes Zeichen — oder nutzt OPENJSON, wo das Trennzeichen beim Umbau zu JSON beliebig lang sein darf.

Ist der XML-Trick heute noch vertretbar?

In neuem Code nicht. Er scheitert an &, < und > im Text, und das fällt oft erst im Produktivbetrieb auf, weil Testdaten diese Zeichen selten enthalten. Im Bestand kann er bleiben, wenn die Daten nachweislich sauber sind — bei einer Überarbeitung ist er der erste Kandidat zum Austausch.

Was ist schneller: STRING_SPLIT oder eine Zahlentabelle?

Für den Normalfall ist STRING_SPLIT als eingebaute Funktion die schnellere und einfachere Wahl. Eine gut indizierte Zahlentabelle kommt nahe heran und schlägt sie in Sonderfällen. Verlässlich ist daran nur eines: Es hängt an Datenmenge, Zeichenkettenlänge und Ausführungsplan — deshalb gehört an diese Stelle eine Messung mit SET STATISTICS IO, TIME ON und nicht eine Faustregel aus einem Forenbeitrag.

Wie mache ich aus mehreren Zeilen wieder eine Zeichenkette?

Mit STRING_AGG, verfügbar ab SQL Server 2017, und dort immer mit WITHIN GROUP (ORDER BY …) — sonst ist die Reihenfolge im Ergebnis nicht zugesichert. Vor 2017 übernahm das der FOR XML PATH-Umweg mit denselben Escaping-Problemen wie der XML-Weg beim Zerlegen.

Liefert STRING_SPLIT leere Werte mit?

Ja. 'a,,b' ergibt drei Zeilen, die mittlere mit einer leeren Zeichenkette. Wer sie nicht braucht, filtert mit WHERE value <> ''. Das ist bewusstes Verhalten und kein Fehler — bei Importdaten ist die leere Position oft eine Information.

Sollte ich Listen dauerhaft als Text speichern?

Nein, wenn darin gesucht, gefiltert oder verknüpft wird. Eine Listenspalte verhindert Indexnutzung und Fremdschlüssel, und Tippfehler fallen nie auf. Der Weg über eine Zuordnungstabelle kostet einmal Aufwand und spart ihn bei jeder Abfrage danach.

Quellen

Die Versions- und Verhaltensangaben stammen aus der Herstellerdokumentation, nicht aus zweiter Hand:

• STRING_SPLIT — Kompatibilitätsgrad 130, einstelliges Trennzeichen, enable_ordinal ab SQL Server 2022
• STRING_AGG — ab SQL Server 2017, WITHIN GROUP (ORDER BY …)
• OPENJSON — Spalten key, value, type
• STRING_ESCAPE — JSON-Sonderzeichen maskieren
• Kompatibilitätsgrad von ALTER DATABASE — welche Funktion welchen Grad braucht

Jetzt loslegen

Zeichenketten zu zerlegen ist selten das eigentliche Thema — dahinter steckt fast immer die Frage, ob mengenbasiert oder zeilenweise gedacht wird, und ob das Modell zur Abfrage passt. Genau daran arbeitet das Seminar SQL Server T-SQL Programmierung: Tabellenwertfunktionen, Ausführungspläne, APPLY und die Umschreibung von Schleifen in mengenbasierte Lösungen. Wer beim Abfragen selbst noch Boden gutmachen will, findet ihn in SQL Fortgeschritten. Und wenn beim Zerlegen Datumswerte als Text auftauchen — der zweite Klassiker unter den Umwandlungsfallen —, hilft unser Beitrag zum SQL-Datumsformat weiter.

Inhouse-Schulungen und der virtuelle Klassenraum werden direkt über die Anfrage vereinbart; offene Seminare mit festen Terminen laufen über unseren Seminarpartner. Welches Format passt, klären wir vorab. Das Angebot richtet sich an Unternehmen, Behörden und Selbstständige; die Anfrage ist unverbindlich und kostenlos.