If your T-SQL is buried under layers of nested REPLACE() calls just to swap out a few characters, there’s a simpler way. SQL Server’s TRANSLATE() function — introduced in 2017 but still rarely used — lets you replace multiple characters in a single string with one function call instead of stacking several REPLACE() functions inside each other.

This guide covers how TRANSLATE() works, how it compares to REPLACE(), and where it falls short compared to other SQL dialects like PostgreSQL and Oracle.

Anyone who has worked with SQL Server and T-SQL will have seen code where REPLACE functions have been nested, sometimes endlessly. The code ends up hard to read, hard to understand and, worse still, easy to make mistakes with during maintenance. It’s often a mess – and in many cases, a mess that can be avoided.

That’s where the TRANSLATE function – introduced to SQL Server back in 2017 – comes in. Nearly a decade on from its introduction, I rarely see the function used in code, but it’s excellent for this use case.

What is the SQL Server TRANSLATE function?

Imagine I have incoming data with phone numbers in the following format:

[02] 9232:4232

In Australia, I’d like to see those phone numbers formatted like this:

(02) 9232-4232

That seems simple enough, but note the code required if I use the REPLACE function:

If it was just one or two replacements, I’d put it all on a single line. Then, as the number of replacements increases, I’ll start to spread it out over a series of lines. This is to try to keep the parentheses matching – and to have some hope of following the code.

The TRANSLATE() function takes three parameters: an input string, a set of characters that should be replaced, and a set of characters to replace them. Here’s how we can use it for the example above:

The characters being matched and replaced are determined by their position in the string.

That’s not all, though. You might now wonder what happens if the characters are matched multiple times. For example…

…will return ((02)) 9232-4232.

REPLACE vs remove in SQL Server

Another reason why I often ended up using nested REPLACE functions in SQL Server was to remove a particular set of characters out of a string.

Perhaps I want to replace the square brackets with round ones – but also want to remove the colon, not replace it. Unfortunately, the SQL Server implementation of the TRANSLATE function doesn’t support this – as much as I wish it did!

Here’s a good example of why this functionality is needed. If I execute this query…

…the following error is returned:

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

In T-SQL, the string of matching characters and the string of replacement characters must be the same length. That’s not the case in other SQL dialects. For more on this, see the official T-SQL documentation (where they are called translations.)

In PostgreSQL, Snowflake, Databricks/Apache Spark SQL, Amazon Redshift, and InterSystems IRIS, if the replacement string is shorter than the matching string, the extra characters are removed. You can even have an empty string for the replacement and just use TRANSLATE to remove a set of characters.

Oracle also allows the shorter replacement string, but you can’t have an empty string for the replacement. That’s treated as NULL so, if you do that, the function returns NULL instead. In my experience, people tend to put a ‘dummy’ matching character first, then use the same character as a replacement.

I do wish SQL Server implemented the TRANSLATE function better, but at least it has the function in the first place – unlike Google BigQuery and MySQL.

Summary

If you’re still writing lots of nested REPLACE functions in SQL Server, it’s time to see if any of them could be replaced (pun intended) by using the little-known TRANSLATE function instead. It’s great for this use case.

All I’d really like to see now is the SQL Server team remove the restriction on requiring the characters and the translations parameters that force them to be the same length.