Contrary to expectations, the TRANSLATE function has nothing to do with translations. It lets you specify multiple string replacements within a single function. It accepts three input arguments:

• input_string: A string expression that needs to be changed
• characters: A string expression representing characters that should be replaced
• translations: A string expression representing characters that will replace characters specified with a second argument

The function returns a string expression of the same type as the first input argument.

In the following example, the TRANSLATE function is used to replace square brackets with parentheses:

SELECT TRANSLATE('[Sales].[SalesOrderHeader]','[]','()');

Here is the output:

(Sales).(SalesOrderHeader)

With the REPLACE function, the same output can be done with a more complex query:

SELECT REPLACE(REPLACE('[Sales].[SalesOrderHeader]','[','('),']',')');

As you can see, the TRANSLATE function lets you define more replacement characters at once. However, the third argument of the function must have the same length as the second. In other words, you cannot use the TRANSLATE function to replace characters with an empty string (as in, to remove characters). Therefore, the following code won't work:

SELECT TRANSLATE('[Sales].[SalesOrderHeader]','[]','');

Instead of the table name formatted with parentheses, you will get the following error message:

Msg 9828, Level 16, State 1, Line 1
The second and third arguments of the TRANSLATE built-in function must contain an equal number of characters.