In SQL Server, the T-SQL TRANSLATE() function returns the string provided as a first argument after some characters specified in the second argument are translated into a destination set of characters specified in the third argument.

It’s similar to the REPLACE() function, but with some important differences.

The TRANSLATE() function requires 3 arguments; the input string, the characters to be replaced, and the characters to replace them.

Syntax

The syntax goes like this:

TRANSLATE ( inputString, characters, translations)

Where inputString is the string that contains the characters to be replaced.

The characters argument specifies which characters should be replaced.

The translations argument specifies what those characters should be replaced with.

Example 1 – Basic Usage

Here’s an example of how it works:

SELECT TRANSLATE('Cat', 'at', 'ow') AS Result;

Result:

+----------+
| Result   |
|----------|
| Cow      |
+----------+

So in this example, the a and t characters were replaced with o and w.

In this case, the the REPLACE() function would have produced the same result. Here are the two functions side by side:

SELECT 
  TRANSLATE('Cat', 'at', 'ow') AS 'TRANSLATE',
  REPLACE('Cat', 'at', 'ow') AS 'REPLACE';

Result:

+-------------+-----------+
| TRANSLATE   | REPLACE   |
|-------------+-----------|
| Cow         | Cow       |
+-------------+-----------+

In this case, the outcome for both functions is the same, but for different reasons.

Here’s what each function did:

• TRANSLATE() replaced a and t (each individual character)
• REPLACE() replaced at (the string)

Example 2 – Mixed Order

This example demonstrates where TRANSLATE() differs from REPLACE(). In this example, I change the order of the characters to be replaced, as well as the characters to replace them:

SELECT 
  TRANSLATE('Cat', 'ta', 'wo') AS 'TRANSLATE',
  REPLACE('Cat', 'ta', 'wo') AS 'REPLACE';

Result:

+-------------+-----------+
| TRANSLATE   | REPLACE   |
|-------------+-----------|
| Cow         | Cat       |
+-------------+-----------+

In this case only the TRANSLATE() function took effect. This is because this function goes through each character one by one. The REPLACE() function on the other hand, looks for the whole string, in exactly the same order.

Example 3 – A More Powerful Example

The following example is based on the Microsoft documentation for the TRANSLATE() function. It demonstrates a scenario where this function has a significant benefit over the REPLACE() function:

SELECT TRANSLATE('2*[3+4]/{7-2}', '[]{}', '()()') AS Result;

Result:

+---------------+
| Result        |
|---------------|
| 2*(3+4)/(7-2) |
+---------------+

To get the equivalent result using the REPLACE() function, we’d need to do this:

SELECT
REPLACE
(
      REPLACE
      (
            REPLACE
            (
                  REPLACE
                  (
                        '2*[3+4]/{7-2}',
                        '[',
                        '('
                  ),
                  ']',
                  ')'
            ),
            '{',
            '('
      ),
      '}',
      ')'
) AS Result;

Result:

+---------------+
| Result        |
|---------------|
| 2*(3+4)/(7-2) |
+---------------+