In SQL Server, the REPLACE() function enables us to replace a string with another string. But what if you want to replace a list of characters with another list of characters?

The TRANSLATE() function might help.

Here’s Microsoft’s explanation of the 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.

Clear as mud?

Example

I think this is one of those times that really shouts out for an example.

SELECT TRANSLATE('Fred [10 points]', '[]', '()');

Result:

Fred (10 points)

Basically, it’s as though we’ve provided a list of values to replace another list of values. But there’s no need to separate each list item with a separator.

Equal Number of Characters

The second and third arguments must contain an equal number of characters.

In other words, you can’t do this:

SELECT TRANSLATE('Fred [10 points]', '[]', '(');

Result:

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.

In this case the second argument contains two characters but the third argument contains just one, so we get an error.

This happens because SQL Server needs to know which character is to replace the second character of the second argument. It goes through each character, one by one, replacing it with the corresponding character from the third argument. If none exists, then it has no option but to throw an error.

Compared with REPLACE()

Here’s an example that illustrates the difference between TRANSLATE() and REPLACE().

SELECT 
  REPLACE('[] [hey]', '[]', '()') AS REPLACE,
  TRANSLATE('[] [hey]', '[]', '()') AS TRANSLATE;

Result:

+-----------+-------------+
 | REPLACE   | TRANSLATE   |
 |-----------+-------------|
 | () [hey]  | () (hey)    |
 +-----------+-------------+ 

The REPLACE() function leaves [hey] exactly as it is, because that whole string wasn’t provided in the second argument. This function only finds a match if the whole string is present.

The TRANSLATE() function on the other hand replaces [hey] with (hey) because it replaces each character one by one. It’s not looking for a whole string to replace, it’s only looking for each individual character individually.