Was tut STING_SPLIT?
In den Wochen hattet ihr hier einige Möglichkeiten kennengelernt, um kommaseparierte Listen in SQL zu erzeugen, neben STUFF in Kombination mit SELECT FOR XML und STRING_AGG auch CONCAT_WS. In dieser Woche möchten wir den umgekehrten Weg gehen und eine Zeichenkette, die kommaseparierte Werte enthält in ihre Bestandteile zerlegen. Seit dem SQL Server 2016 gibt es in der relationalen Datenbankengine von Microsoft mit STRING_SPLIT eine Möglichkeit, das zu tun.
Wie verwendet man STRING_SPLIT?
Bis zum SQL Server 2016 musstet ihr, wenn ihr Strings zerlegen wolltet, üblicherweise eine benutzerdefinierte Funktion schreiben, die wahlweise in einer WHILE-Schleife über CHARINDEX und SUBSTRING operierte oder wie folgt die Liste in ein XML-Objekt umwandelte, das sich abfragen ließ:
SELECT item = y.i.value('(./text())[1]', 'nvarchar(4000)')
FROM
(
SELECT x = CONVERT(XML, ''
+ REPLACE('Akio,Hiraku,Kazuo', ',', '')
+ '').query('.')
) AS a CROSS APPLY x.nodes('i') AS y(i)
Inzwischen ist das viel einfacher geworden, denn der SQL Server bietet mit STRING_SPLIT eine Methode, um eine kommaseparierte Liste in ihre Bestandteile zu zerlegen ohne weitere Klimmzüge zu betreiben. Die STRING_SPLIT-Funktion hat als Rückgabewert dabei eine Tabelle mit genau einer Spalte, die “value” heißt und die einzelnen Bestandteile der Zeichenkette enthält. Um die Bestandteile der Zeichenkette zu sehen, müsst ihr die “value”-Spalte aus dieser Rückgabe abfragen:
SELECT [value] FROM STRING_SPLIT('Akio,Hiraku,Kazuo', ',')
Wichtig ist dabei, dass anders als beispielsweise die REPLACE-Funktion, die STRING_SPLIT-Funktion nur eine Zeichenkette mit einer Länge von eins als zweites Argument, also als Trennzeichen akzeptiert. Es ist also nicht möglich, einen HTML-String beispielsweise nach
SELECT [value] FROM STRING_SPLIT('Akio,Hiraku,,Kazuo', ',')
In diesem Fall enthält eure Rückgabe-Tabelle eine Zeile mit einem leeren String, allerdings keinen NULL-Wert. Selbiges passiert euch, wenn die Zeichenkette mit einem Komma abschließt:
SELECT [value] FROM STRING_SPLIT('Akio,Hiraku,,Kazuo,', ',')
Wollt ihr diese leeren Rückgaben unterbinden, so müsst ihr sie über eine WHERE-Bedingung filtern, das ist natürlich möglich, da ihr ja eine Abfrage auf dem Rückgabewert ausführt. Dafür lassen wir uns zunächst die Länge der einzelnen Teile ausgeben:
SELECT
[value]
,LEN(TRIM([value])) AS [Length]
FROM STRING_SPLIT('Akio,Hiraku,,Kazuo,', ',')
Auf diese Länge könnt ihr nun natürlich die Ergebnismenge filtern:
SELECT
[value]
,LEN(TRIM([value])) AS [Length]
FROM STRING_SPLIT('Akio,Hiraku,,Kazuo,', ',')
WHERE LEN(TRIM([value])) > 0
Nutzt ihr dies nun in Kombination mit STRING_AGG, so könnt ihr beispielsweise eine kommaseparierte Liste bereinigen und die leeren Werte sowie hinten stehende Kommas filtern:
SELECT
STRING_AGG([value], ',')
FROM STRING_SPLIT('Akio,Hiraku,,Kazuo,', ',')
WHERE LEN(TRIM([value])) > 0
Nachdem ihr nun in den letzten Wochen viele Methoden kennengelernt hattet, um einzelne Strings zu Zeichenketten zu verknüpfen, habt ihr nun die gegenteilige Funktion kennengelernt, um eine Zeichenkette wieder in ihre Bestandteile zu zerlegen. Nutzt dieses Katana um eure kommaseparierten Zeichenketten zukünftig ohne hässliche benutzerdefinierte Funktionen fachgerecht zu zerlegen.
