Was ist TRANSLATE (nicht)?
Leider ist TRANSLATE nicht die T-SQL Syntax als Frontend für die Cognitive Services Translator API. Das heißt ihr könnt die TRANSLATE-Funktion nicht verwenden, um eure Produktbeschreibungen in Suaheli zu übersetzen. Schade, doch dafür bräuchtet ihr die Common Language Runtime im SQL Server (CLR, vielleicht wäre das ja auch mal einen Ausflug ins Dojo wert…). Stattdessen ist die TRANSLATE-Funktion eine Möglichkeit, wie ihr viele geschachtelte REPLACE-Statements deutlich vereinfachen könnt, denn sie ersetzt Zeichen in Zeichenketten.
Wie verwendet man TRANSLATE?
Beginnen wir mit einem einfachen Beispiel, nehmen wir an, dass ihr in einer Zeichenkette ein Zeichen durch ein anderes Zeichen ersetzen möchtet. Das könnt ihr sowohl mit REPLACE als auch mit TRANSLATE tun:
SELECT
REPLACE('Duju', 'u', 'o') AS [Replaced]
,TRANSLATE('Duju', 'u', 'o') AS [Translated]
In beiden Spalten der Ergebnismenge werdet ihr nun das Wort Dojo stehen haben, da in beiden Funktionsaufrufen der Suchstring “u” durch “o” ersetzt wurde. Doch wofür haben wir dann zwei Funktionen? Ganz einfach, weil die beiden Funktionen nicht dasselbe tun. Versuchen wir etwas anderes zu tun, nehmen wir uns das Wort Dojo mit einem Ausrufezeichen am Ende und versuchen wir, das Ausrufezeichen zu entfernen:
SELECT
REPLACE('Dojo!', '!', '') AS [Replaced]
SELECT
TRANSLATE('Dojo!', '!', '') AS [Translated]
Während beim ersten Ausdruck wie gewünscht das Ausrufezeichen entfernt (durch einen leeren String ersetzt) wurde, liefert die zweite Abfrage eine Fehlermeldung, dass die beiden letzten Argumente von TRANSLATE gleich viele Zeichen enthalten müssen. Anscheinend gibt es hier also doch noch weitere Unterschiede. Diese sehen wir uns an, indem wir einen weiteren Tippfehler in unser Dojo einbauen und versuchen, aus “Duku” das “Dojo” zu machen. Versuchen wir, also die “u”s durch “o”s und das “k” durch ein “j” zu ersetzen.
Der Naive Ansatz wäre folgender:
SELECT
REPLACE('Duku', 'uk', 'oj') AS [Replaced]
,TRANSLATE('Duku', 'uk', 'oj') AS [Translated]
Und während hier im ersten Fall ein Doju herauskommt, liefert der zweite Ansatz tatsächlich das gewünschte “Dojo” zurück. Warum ist das so? Weil REPLACE immer den gesamten Suchstring sucht und versucht, ihn zu ersetzen. Also wenn das zweite Argument in Gänze im ersten Argument enthalten ist, wird es durch das dritte Argument ersetzt. Bei TRANSLATE dagegen wird zeichenweise gesucht und ersetzt, im Beispiel also jedes “u” durch ein “o” und jedes “k” durch ein “j”. Das erklärt auch, warum wir im vorangegangen Beispiel einen Fehler erhalten haben, denn wenn wir 1:1 ersetzen, brauchen wir natürlich auch für jedes Zeichen einen Ersatz. Noch deutlicher wird das, wenn wir uns vor Augen halten, dass es hier nicht auf die Reihenfolge ankommt, in der gesucht wird:
SELECT
REPLACE('123', '321', '654') AS [Replaced]
,TRANSLATE('123', '321', '654') AS [Translated]
Diese Abfrage liefert in der Replace-Spalte “123” zurück, da der Suchstring “321” nicht gefunden wurde, in der Translate-Spalte hingegen steht “456”, da die “3” durch eine “6”, die “2” durch eine “5” und die “1” durch eine “4” ersetzt wurde.
Natürlich könnt ihr dieses Verhalten auch mit geschachtelten REPLACE-Statements erreichen:
SELECT
REPLACE('123', '321', '654') AS [Replaced]
,TRANSLATE('123', '321', '654') AS [Translated]
,REPLACE(
REPLACE(
REPLACE('123', '1', '4'),
'2', '5'),
'3', '6') AS [Replaced2]
Doch jeder wird zugeben, dass die Syntax mit den geschachtelten REPLACE-Statements deutlich schlechter zu lesen ist, als die TRANSLATE-Syntax. Außerdem kommt es hier bei geschachtelten REPLACE durchaus auf die Reihenfolge an, das können wir sehen, wenn wir den String “abc” um 1 shiften möchten, also aus “abcba” “bcdcb” machen. Mit Translate ginge das wie folgt:
SELECT
TRANSLATE('abcba', 'abc', 'bcd') AS [Translated]
Wenn wir mit REPLACE nicht den gesamten String ersetzen möchten, dann müssten wir wie oben die REPLACE-Funktionsaufrufe schachteln:
SELECT
TRANSLATE('abcba', 'abc', 'bcd') AS [Translated]
,REPLACE(
REPLACE(
REPLACE(
'abcba', 'a', 'b'
), 'b', 'c')
, 'c', 'd') AS [Replaced]
Allerdings haben wir hier nicht das gewünschte Resultat, den wir ersetzen zuerst die “a”s durch “b”s und haben dann die Zeichenkette “bbcbb”, in der ersetzen wir dann die “b”s durch “c”s und haben “ccccc”, wo wir die “c”s durch “d”s ersetzen und “ddddd” erhalten. Stattdessen müssten wir verkehrt herum schachteln:
SELECT
TRANSLATE('abcba', 'abc', 'bcd') AS [Translated]
,REPLACE(
REPLACE(
REPLACE(
'abcba', 'a', 'b'
), 'b', 'c')
, 'c', 'd') AS [Replaced]
,REPLACE(
REPLACE(
REPLACE(
'abcba', 'c', 'd'
), 'b', 'c')
, 'a', 'b') AS [Replaced2]
Ihr seht also bei solchen zeichenweisen Ersetzungen ist es durchaus wichtig, was ihr in welcher Reihenfolge ersetzt. Also seid zumindest vorsichtig bei geschachtelten REPLACE-Statements – oder verwendet stattdessen TRANSLATE.
Was ihr darüber hinaus mit TRANSLATE tun könnt ist beispielsweise Klammern zu ersetzen:
SELECT
TRANSLATE('f[x] = {1,2,3}', '[]{}', '()()')
Hier erhaltet ihr die Zeichenkette “f(x) = (1,2,3)” zurück.
Ihr seht: Wunder könnt ihr von TRANSLATE nicht erwarten, aber um in Zeichenketten Ersetzungen vorzunehmen, ist die Funktion durchaus eine Erleichterung. Also ab ins TRANSLATE('Duku', 'uk', 'oj') und ausprobieren.
