What is TRANSLATE (not)?
Unfortunately, ‘TRANSLATE’ is not the T-SQL syntax as frontend for the Cognitive Services Translator API. That means you cannot use the TRANSLATE function to translate your product descriptions into Swahili. Too bad, but for this you would need the Common Language Runtime in the SQL Server (CLR, maybe this would be worth a trip to the Dojo…). Instead, the ‘TRANSLATE’ function is a way how you can simplify many nested REPLACE statements, because it replaces characters in strings.
How to use TRANSLATE
Starting with a simple example, let’s assume you want to replace a character in a string with another character. You can do this with both REPLACE and TRANSLATE:
SELECT
REPLACE('Duju', 'u', 'o') AS [Replaced]
,TRANSLATE('Duju', 'u', 'o') AS [Translated]
In both columns of the result set you will now have the word Dojo, because in both function calls the search string “u” was replaced by “o”. But what do we have two functions for then? Simply because the two functions do not do the same thing. Let’s try to do something else, take the word Dojo with an exclamation mark at the end and try to remove the exclamation mark:
SELECT
REPLACE('Dojo!', '!', '') AS [Replaced]
SELECT
TRANSLATE('Dojo!', '!', '') AS [Translated]
While the exclamation mark was removed (replaced by an empty string) as desired in the first expression, the second query returns an error message that the last two arguments of TRANSLATE must contain the same number of characters. Apparently there are other differences here after all. Let’s take a look at them by adding another typo to our Dojo and try to make “Duku” the “Dojo”. So let’s try to replace the “u “s with “o “s and the “k” with a “j”.
The naive approach would be the following:
SELECT
REPLACE('Duku', 'uk', 'oj') AS [Replaced]
,TRANSLATE('Duku', 'uk', 'oj') AS [Translated]
And while here in the first case a doju comes out, the second approach actually returns the desired “dojo”. Why is that? Because REPLACE always searches the entire search string and tries to replace it. So if the second argument is contained entirely in the first argument, it is replaced by the third argument. With TRANSLATE, on the other hand, the search and replacement is character by character, so in the example, every “u” is replaced by an “o” and every “k” by a “j”. This also explains why we got an error in the previous example, because if we replace 1:1, we need a replacement for every character, of course. This becomes even clearer if we keep in mind that the order in which we search is not important here:
SELECT
REPLACE('123', '321', '654') AS [Replaced]
,TRANSLATE('123', '321', '654') AS [Translated]
This query returns “123” in the Replace column, because the search string “321” was not found. In the Translate column, however, it returns “456” because the “3” was replaced by a “6”, the “2” by a “5” and the “1” by a “4”.
Of course you can achieve this behavior with nested REPLACE statements:
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]
But everyone will admit that the syntax with the nested REPLACE statements is much harder to read than the TRANSLATE syntax. Besides, with nested REPLACE it depends on the order, we can see that if we want to shift the string “abc” by 1, i.e. make “bcdcb” from “abcba”. With TRANSLATE this would work as follows:
SELECT
TRANSLATE('abcba', 'abc', 'bcd') AS [Translated]
If we do not want to replace the entire string with REPLACE, we would have to nest the REPLACE function calls as above:
SELECT
TRANSLATE('abcba', 'abc', 'bcd') AS [Translated]
,REPLACE(
REPLACE(
REPLACE(
'abcba', 'a', 'b'
), 'b', 'c')
, 'c', 'd') AS [Replaced]
However, we do not have the desired result here, because we first replace the “a “s with “b “s and then have the string “bbcbb”, in which we replace the “b “s with “c “s and have “ccccc”, where we replace the “c “s with “d “s and get “ddddd”. Instead, we would have to nest upside down:
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]
So you see with such character-wise replacements it is quite important what you replace in which order. So at least be careful with nested REPLACE statements – or use TRANSLATE instead.
Another thing you can do with TRANSLATE is to replace brackets:
SELECT
TRANSLATE('f[x] = {1,2,3}', '[]{}', '()()')
Here you get the string “f(x) = (1,2,3)” back.
You see: You can’t expect miracles from TRANSLATE, but to replace strings, this function is a relief. So go to TRANSLATE('Duku', 'uk', 'oj') and try it out.
