STUFF

Was ist STUFF?

STUFF ist eine Funktion zur Manipulation von Zeichenketten. Die Funktion “stopft” eine Zeichenkette in eine andere, daher der recht bildlich zu verstehende Name. Traditionell gehört String-Manipulation nicht unbedingt zu den großen Stärken von relationalen Datenbanken und der SQL-Syntax, STUFF gehört zu den Funktionen aus diesem Bereich, die euch, wenn ihr sie beherrscht das Leben deutlich einfacher machen können.

Was könnt ihr mit STUFF so machen?

Die STUFF-Funktion bekommt vier Argumente übergeben:

• die Zeichenkette, die ihr bearbeiten möchtet,
• die Position ab der ihr einen neuen Text in die Zeichenkette einfügen möchtet,
• die Anzahl der Zeichen, die ihr in der Zeichenkette ersetzen möchtet und
• den String, mit dem ihr sie ersetzen möchtet.

Doch genug der Theorie, sehen wir uns die STUFF-Funktion einmal im Detail an. Ein Aufruf der Funktion könnte wie folgt aussehen:

SELECT STUFF('Show me your execution plan', 1, 7, 'What''s')

Das Ergebnis dieser Abfrage ist “What’s your execution plan”. Wir haben also ab dem ersten Zeichen sieben Zeichen ersetzt also “Show me” und an ihre Stelle die Zeichenfolge “What’s” gesetzt. Natürlich könntet ihr das auch so lösen:

SELECT 'What''s'+SUBSTRING('Show me your execution plan',8,20)

Doch zum Einen müsst ihr hier, wenn ihr nicht gerade an den Anfang einer Zeichenfolge einfügt ziemlich wilde Index-Operationen auf euren Strings durchführen und zum anderen ist die Lesbarkeit bei solchen Operationen nicht unbedingt die höchste. Doch einfach nur Text in Zeichenketten zu ersetzen ist zwar ganz nett, mit der STUFF-Funktion können wir aber noch wesentlich mehr tun. Stellt euch vor, ihr habt ein Datum im Format YYYYMMDD gegeben und möchtet das lesbar darstellen, dann könnt ihr natürlich auch hier mit Substrings arbeiten:

SELECT SUBSTRING('20200514', 1, 4)+'-'+SUBSTRING('20200514',5,2)+'-'+SUBSTRING('20200514',7,2)

Spätestens hier sieht das geübte Ninja-Auge, dass die Syntax mit STUFF zum einen wesentlich kürzer ist und zum anderen auch nur einmal die Zeichenfolge enthält, mit der ihr arbeitet, was die Lesbarkeit und die Wartbarkeit durchaus erhöht.

Auch zum Maskieren von Daten beispielsweise in Views könnt ihr STUFF gut verwenden, stellt euch vor, ihr möchtet in einer Anwendung auf der WideWorldImporters Datenbank von euren Usern nur die Vornamen zurückgeben, die Nachnamen aber mit X maskieren. Das könnt ihr dann mit STUFF wie folgt lösen:

SELECT 
     FullName
    ,STUFF(
        FullName, 
        CHARINDEX(' ',FullName)+1, 
        LEN(FullName)-CHARINDEX(' ',FullName)+1, 
        REPLICATE('X', LEN(FullName)-CHARINDEX(' ', FullName)))
FROM [Application].[People]

In dieser Abfrage ersetzt ihr in der Spalte FullName die Zeichen ab dem ersten Leerzeichen bis zum Ende der Zeichenfolge durch X. So haben eure Namen immer noch die richtige Länge, sind aber beispielsweise für externe Entwickler maskiert, was die potenziellen Probleme mit dem Datenschutz minimiert.

Doch die meisten von euch werden der STUFF-Funktion sicher schon über den Weg gelaufen sein, wenn es darum ging, aus den Werten einer Spalte eine kommaseparierte Liste zu erzeugen. Wie das funktioniert, möchten wir nun einmal im Detail auseinandernehmen.

Zunächst müssen wir versuchen, die verschiedenen Zeilen in einem Ergebnis zusammenzufassen, mit dem wir arbeiten können. Dafür eignet sich die SELECT FOR XML-Funktionalität, fassen wir also die Namen der Transaktionstypen in der WideWorldImporters Datenbank in einem XML-Objekt zusammen:

SELECT TransactionTypeName 
FROM [Application].[TransactionTypes] 
FOR XML PATH ('')

Ihr erhaltet nun ein XML-Objekt, das für jeden Transaktionstypen einen Eintrag mit dem Tag “TransactionTypeName” hat. Um diese Tags loszuwerden müsst ihr dafür sorgen, dass die Spalte mit der ihr arbeitet keinen Namen mehr hat. Das könnt ihr tun, indem ihr eine Funktion aufruft oder den Wert der Spalte modifiziert. Modifizieren wir den Wert also, indem wir ein Komma voranstellen:

SELECT ','+TransactionTypeName 
FROM [Application].[TransactionTypes] 
FOR XML PATH ('')

Das entspricht schon beinahe der gesuchten kommaseparierten Liste nur dass sie ein führendes Komma enthält. Das könnt ihr aber mit STUFF ganz einfach loswerden, indem ihr ab Zeichen 1 ein Zeichen durch einen leeren String ersetzt. Dafür müsst ihr die STUFF-Funktion auf das Ergebnis dieser Abfrage anwenden (diese Abfrage also als Argument an die STUFF-Funktion übergeben) und dann das Ergebnis der STUFF-Funktion wiederum Abfragen, um es anzuzeigen. Das Ganze sieht im Resultat dann so aus:

SELECT 
    STUFF(
        (SELECT ','+TransactionTypeName
            FROM [Application].[TransactionTypes] 
            FOR XML PATH ('')
        ), 1, 1, '')

Wenn ihr diese Werte nun noch an die Zeilen einer Tabelle joinen möchtet, müsst ihr das äußere Select entsprechend anpassen:

SELECT
    STUFF(
        (SELECT ','+TransactionTypeName
            FROM [Application].[TransactionTypes] 
            FOR XML PATH ('')
        ), 1, 1, '')
FROM [Application].[TransactionTypes]

Im heutigen Dojo habt ihr hoffentlich gelernt, dass die STUFF-Funktion durchaus hilfreich sein kann, wenn es darum geht, in Strings zu ersetzen, einzufügen oder zu löschen. Ein Blick auf diese Funktion lohnt sich also, wenn eure Entwickler mal wieder mit abgefahrenen Anforderungen hinsichtlich der Manipulation von Zeichenfolgen auf euch zukommen…

Referenzen

• Dokumentation von Microsoft