Starting with SQL Server 2017, you can now use the T-SQL TRANSLATE() function to translate one or more characters into another set of characters.
At first glance, you might think that the TRANSLATE() function does exactly the same thing as the REPLACE() function, but there are significant differences between the two.
Definitions
First, let’s look at the definition of each function:
REPLACE()- Replaces all occurrences of a specified string value with another string value.
TRANSLATE()- Returns the string provided as a first argument after some characters specified in the second argument are translated into a destination set of characters.
The main difference is how each function deals with multiple characters. REPLACE() replaces one string with another string. Therefore, if a string contains multiple characters, each character must be in the same order. TRANSLATE() on the other hand, replaces each character one by one, regardless of the order of those characters.
Example – Same result
There are some cases where both functions will return the same result. Like this:
SELECT
REPLACE('123', '123', '456') AS Replace,
TRANSLATE('123', '123', '456') AS Translate;
Result:
Replace Translate ------- --------- 456 456
In this case, REPLACE() returns 456 because the exact string in the second argument matched a string in first argument (in this case, it was the whole string).
TRANSLATE() returns 456 because each character in the second argument is present in the first argument.
Example – Different Result
Now for an example that demonstrates one of the differences between TRANSLATE() and REPLACE():
SELECT
REPLACE('123', '321', '456') AS Replace,
TRANSLATE('123', '321', '456') AS Translate;
Result:
Replace Translate ------- --------- 123 654
In this case, REPLACE() has no effect (it returns the original string) because the second argument is not an exact match for the first argument (or a substring within it). Even though the second argument contains the correct characters, they are not in the same order as the first argument, and therefore, the whole string doesn’t match.
TRANSLATE() does have an effect because each character in the second argument is present in the first argument. It doesn’t matter that they’re in a different order, because each character is translated one by one. SQL Server translates the first character, then the second, then the third, and so on.
Non-Contiguous Strings
Similar to the previous example, you can also get different results when the first argument contains the characters in the second argument, but they are non-contiguous:
SELECT
REPLACE('1car23', '123', '456') AS Replace,
TRANSLATE('1car23', '123', '456') AS Translate;
Result:
Replace Translate ------- --------- 1car23 4car56
Arguments of a Different Length
You may also get different results between each function whenever there are discrepancies in the number of characters in the various arguments.
Here’s an example where the first argument contains less characters than the second and third arguments:
SELECT
REPLACE('123', '1234', '4567') AS Replace,
TRANSLATE('123', '1234', '4567') AS Translate;
Result:
Replace Translate ------- --------- 123 456
In this case, REPLACE() has no effect because the second argument contains more characters than the first argument. Therefore, it’s impossible for the first argument to contain the second argument.
The TRANSLATE() function however, does have an effect in this case. This is because the second argument contains characters that are in the first argument. It doesn’t matter that the second argument contains more characters than the first. The most important thing is that the third argument contains the same number of characters as the second.
There are also cases when REPLACE() works perfectly but TRANSLATE() throws an error.
Example:
SELECT REPLACE('1234', '123', '4567') AS Replace;
Result:
Replace ------- 45674
In this case, REPLACE() works as expected.
However, if we provide the same arguments to TRANSLATE(), we get an error:
SELECT TRANSLATE('1234', '123', '4567') AS Translate;
Result:
Error: The second and third arguments of the TRANSLATE built-in function must contain an equal number of characters.
As the error message states, the second and third arguments must contain an equal number of characters.
When Should I Use REPLACE()?
You should use REPLACE() when you need to replace all occurrences of a specific string, exactly as it is written. For example, changing someone’s name to another name.
Using TRANSLATE() in such cases can have disastrous results:
SELECT
REPLACE('Homer Simpson', 'Homer', 'Jason') AS Replace,
TRANSLATE('Homer Simpson', 'Homer', 'Jason') AS Translate;
Result:
Replace Translate ------------- ------------- Jason Simpson Jason Sispsan
When Should I Use TRANSLATE()?
As demonstrated in the previous example, the TRANSLATE() function can be useful if you need to replace all occurrences of each character specified, regardless of their order within the original string.
It can also be used in place of REPLACE() to simply the code. Here’s an example (based on an example on the Microsoft website):
SELECT
REPLACE(REPLACE(REPLACE(REPLACE('2*[3+4]/{7-2}','[','('), ']', ')'), '{', '('), '}', ')') AS Replace,
TRANSLATE('2*[3+4]/{7-2}', '[]{}', '()()') AS Translate;
Result:
Replace Translate ------------- ------------- 2*(3+4)/(7-2) 2*(3+4)/(7-2)