SQL - Subtile Unterschiede in Serverfunktionen Teil 2

Oft hat man schon teilweise jahrelang mit einer bestimmten Serverfunktion gearbeitet, um dann durch Zufall herauszufinden, es gibt auch eine andere, die zumindest scheinbar genau das Gleiche erledigt. Ein paar dieser AHA-Erlebnisse und einen kurzen Überblick über Ähnlichkeiten und Unterschiede gibt es hier:

Im ersten Teil haben wir uns angesehen, worin der Unterschied zwischen der Verwendung des Plus-Zeichens und der CONCAT-Funktion bzw. CONCAT und CONCAT_WS besteht, bzw. wofür und wie PARSE, CAST und CONVERT verwendet werden.

 

In diesem Teil beschäftigen wir uns mit ISNULL vs. COALESCE, CHARINDEX vs. PATINDEX und TRANSLATE vs. REPLACE.

 

ISNULL und COALESCE

 

Sowohl ISNULL als auch COALESCE sollen uns helfen, etwas in unsere Textausgabe zu schreiben, wenn in einem Feld kein Wert enthalten ist (der Eintrag also NULL ist).

Wo liegen die Unterschiede? Eine kleine Testtabelle kann uns helfen:

CREATE TABLE Hunde (Id int identity, Hund nvarchar(30), Hunderasse nvarchar(30), Fell nvarchar(30))

 

INSERT INTO Hunde (Hund, Hunderasse, Fell)

VALUES     ('Tasso', 'DSH', NULL),

           ('Waldi', 'Dackel', 'kurz'),

           ('Mina', NULL, 'mittellang'),

           ('Luna', NULL, NULL)


Wir fragen die Hunderasse ab, und für den Fall, dass keine eingetragen ist, können wir etwas eintragen, z.B. „Mischling“:

-- mit ISNULL:

SELECT ISNULL(Hunderasse, 'Mischling') AS Hunderasse

FROM Hunde

 

-- mit COALESCE:

SELECT COALESCE(Hunderasse, 'Mischling') AS Hunderasse

FROM Hunde


Bei beiden Abfragen erhalten wir das gleiche Ergebnis:


Aber angenommen, wir wollen keinen Text eingeben, sondern beispielsweise die Kategorie 1, dann sieht das Ergebnis plötzlich anders aus.

-- mit ISNULL:

SELECT ISNULL(Hunderasse, 1) AS Hunderasse

FROM Hunde


Ergebnis:


Erwartungsgemäß wird NULL mit unserem Eintrag (1) ersetzt.

Bei COALESCE bekommen wir allerdings eine Fehlermeldung:

 -- mit COALESCE:

SELECT COALESCE(Hunderasse, 1) AS Hunderasse

FROM Hunde

 

Ergibt: „Conversion failed when converting the nvarchar value 'DSH' to data type int.

 

Daran erkennen wir schon einen ersten Unterschied zwischen den beiden Funktionen. ISNULL evaluiert den ersten Ausdruck, in unserem Fall ein nvarchar, und entscheidet, dass man 1 doch auch zu Text machen kann.

COALESCE evaluiert alle Ausdrücke und entscheidet sich für die höchste Präzedenz (Rangfolge; engl.: precedence). In diesem Fall „gewinnt“ der Integerwert.

Mehr zum Thema Rangfolge von Datentypen gibt es in der Microsoft-Dokumentation.


Noch einen anderen Unterschied zwischen den beiden Funktionen können wir ganz leicht an unserem Beispiel beobachten. ISNULL kann nur eine, COALESCE mehrere Spalten überprüfen.

ISNULL ersetzt alle NULL innerhalb der ausgewählten Spalte mit unserem selbstdefinierten Eintrag; COALESCE kann diese Überprüfung für mehrere Spalten ausführen. Dabei wird der zweite Ausdruck übernommen, wenn im ersten NULL steht, der dritte, wenn in den beiden ersten NULL steht und so weiter. Das ist möglich, weil COALESCE im Hintergrund ein CASE-Statement verwendet.

 

Bei unserem Hundesalon-Beispiel würden wir mit ISNULL eine Fehlermeldung bekommen, wenn wir mehr als 2 Parameter übergeben:

 SELECT ISNULL(Hunderasse, Fell, 'unbekannt') AS Fell
     , Fell
     , Hunderasse
FROM Hunde


Fehlermeldung: The isnull function requires 2 argument(s).

 

Im SSMS wird unser SELECT-Statement auch gleich unterwellt und auch der Tooltip weist uns darauf hin, dass hier nur 2 Parameter (2 arguments) erwartet werden.

Mit COALESCE dürfen wir so viele Spalten überprüfen, wie wir wollen:

SELECT COALESCE(Fell, Hunderasse, 'unbekannt') AS Fell

     , Fell

     , Hunderasse

FROM Hunde


Das bedeutet, wenn keine Felllänge eingetragen ist, dann soll einfach die Hunderasse ausgegeben werden, wenn weder Felllänge noch Hunderasse eingetragen sind, dann soll „unbekannt“ ausgegeben werden:


Beide haben natürlich ihre Berechtigung, und man muss von Fall zu Fall entscheiden, welche der beiden Funktionen dafür besser geeignet ist. Haben wir aber eine Anforderung, die sich mit beiden Funktionen lösen lässt, dann besser mit ISNULL: Im direkten Vergleich ist ISNULL etwas schneller, da im Hintergrund kein CASE abläuft.

 



CHARINDEX und PATINDEX



Wieder zwei von jenen Funktionen, die recht ähnlich klingen und ähnlich, aber eben nicht gleich, verwendet werden. Tatsächlich sind sie für unterschiedliche Anwendungsbereiche und Lösungen gedacht.

 

Mit CHARINDEX suchen wir nach dem ersten Vorkommnis eines bestimmten Zeichens.

 SELECT CHARINDEX('a', 'Donaudampfschifffahrtsgesellschaft') AS [Stelle von "A"]

Ergebnis:


In „Donaudampfschifffahrtsgesellschaft“ kommen noch weitere „a“ vor, uns wird von CHARINDEX aber nur die erste Stelle angegeben, an der das gesuchte Zeichen (oder auch die gesuchte Zeichenfolge) auftritt.

Was CHARINDEX im Gegensatz zu PATINDEX noch zu bieten hat, ist ein dritter, optionaler Parameter: start_location. Wir können angeben, ab welcher Stelle gesucht werden soll. Uns interessieren beispielsweise nicht die As in „Donaudampfschiff“, wir wollen aber feststellen, ob es danach noch weitere gibt, dann können wir angeben, dass nach „a“ erst ab der Stelle 17 gesucht werden soll:

SELECT CHARINDEX('a', 'Donaudampfschifffahrtsgesellschaft', 17) AS [Stelle von "A"]


Ergebnis: 18 (das „a“ in „fahrt“).

 

Auch mit PATINDEX können wir nach unserem „a“ suchen. Allerdings müssen wir das ein wenig anders schreiben. Wenn wir es genauso machen, wie bei CHARINDEX, geht es schief:

SELECT PATINDEX('a', 'Donaudampfschifffahrtsgesellschaft') AS ["A" mit PATINDEX]


 Ergebnis:


Es scheint so, als käme hier kein A vor.

Das liegt daran, dass unser CHARINDEX nach genau diesem Zeichen sucht, PATINDEX hingegen nach einem Pattern – PATINDEX macht ein LIKE! Wir hätten also mit der Anweisung oben nach genau „a“ und sonst nichts gesucht. Wollen wir das erste „a“ innerhalb von „Donaudampfschifffahrtsgesellschaft“ finden, sollten wir CHARINDEX verwenden. Wollen wir es aus irgendeinem Grund unbedingt mit PATINDEX lösen, müssten wir das so schreiben:

SELECT PATINDEX('%a%', 'Donaudampfschifffahrtsgesellschaft') AS ["A" mit PATINDEX]


Auch damit bekommen wir wieder die erste Stelle heraus, an der sich das gesuchte Zeichen befindet (4).

Dafür kann PATINDEX aber etwas, was CHARINDEX nicht kann: Richtig – nach Patterns suchen. Da PATINDEX ein LIKE zugrunde liegt, können wir hier Wildcards verwenden.


Wir suchen entweder ein A oder ein I, das vor einem F kommt:

SELECT PATINDEX('%[ai]f%', 'Donaudampfschifffahrtsgesellschaft')


Ergebnis: 14 (das „i“ in „schifffahrt“).




TRANSLATE und REPLACE



Mit diesen beiden Funktionen lassen sich Zeichen austauschen/ersetzen. Hier kann es wieder einmal im ersten Moment so aussehen, als würden beide Funktionen gleich arbeiten…

 SELECT     TRANSLATE('Ship', 'shi', 'cla') AS [Translate]
       , REPLACE('Ship', 'shi', 'cla') AS [Replace]


Ergebnis: 


Gleiches Ergebnis? Ja, aber nur zufälligerweise bei diesem konkreten Beispiel. Eine winzige Änderung zeigt, dass sich die beiden Funktionen doch anders verhalten:

SELECT TRANSLATE('Ship', 'his', 'lac') AS [Translate]
     , REPLACE('Ship', 'his', 'lac') AS [Replace]



Ergebnis:


TRANSLATE ersetzt jeden Buchstaben aus dem zweiten Parameter mit dem korrelierenden Buchstaben aus dem 3. Parameter, also in unserem Fall „h“ mit „l“, „i“ mit „a“ und „s“ mit „c“ – unabhängig davon, wo im Input-String sie vorkommen.

REPLACE sucht nach der genauen Zeichenfolge „his“, und würde sie mit „lac“ ersetzen; da diese Zeichenfolge aber in „Ship“ nicht vorkommt, wird der ursprüngliche String ausgegeben.

Einen weiteren Unterschied sehen wir, wenn Parameter 2 und 3 unterschiedlich viele Zeichen enthalten:

SELECT TRANSLATE('Ship', 'sh', 'cla') AS [Translate]


 

TRANSLATE wirft in diesem Fall eine Fehlermeldung aus: The second and third arguments of the TRANSLATE built-in function must contain an equal number of characters.

 

REPLACE ersetzt die Zeichen „sh“ durch „cla“… mit einem mehr oder weniger erfolgreichen Ergebnis:

SELECT REPLACE('Ship', 'sh', 'cla') AS [Replace]


Ergebnis:


Vorsicht besonders bei längeren Zeichenfolgen oder mehreren Wörtern – TRANSLATE ersetzt alle gefundenen Zeichen; REPLACE nur die exakte Zeichenfolge. Das kann mit TRANSLATE auch katastrophale Folgen haben:

SELECT     TRANSLATE('Wolfgang Amadeus Mozart', 'Amadeus', 'Norbert') AS [Translate]     
              , REPLACE('Wolfgang Amadeus Mozart', 'Amadeus', 'Norbert') AS [Replace]



Ergebnis:


REPLACE sollte also verwendet werden, um exakte Zeichenfolgen zu ersetzen; TRANSLATE eignet sich für das Auswechseln aller angegebenen Zeichen, egal, wo innerhalb der Search-Expression sie stehen.


Mehr zum Thema SQL gibt es in unseren Kursen!


In der Zwischenzeit wünsche ich wie immer viel Spaß beim Ausprobieren!




Kommentare sind geschlossen