Replacing REPLACE()s with TRANSLATE() in SQL Server

Greg Low makes use of an uncommon function:

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.

One important thing to remember with TRANSLATE() is that it’s a character-for-character replacement. In other words, if you TRANSLATE('ABCDEFG', 'x'), it will replace every instance of each of those characters with the letter “x.” It doesn’t replace a substring.