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!