Click on the banner to learn about and purchase my database training on Azure

SQL Server – Using TRANSLATE to replace multiple REPLACE

Views: 236
Reading Time: 6 minutes

Hello everybody!
In this quick post here, I'd like to share with you some use cases of the TRANSLATE function, available since SQL Server 2017, which can help replace multiple REPLACE commands with just one TRANSLATE command, greatly reducing code size and complexity.

Spoiler: Do you use a version before 2017? Calm down. There is a way for everything 🙂

To understand how the TRANSLATE command works and its differences to REPLACE, I've prepared some cool examples for you.

TRANSLATE Simple

In this first example, we will demonstrate how TRANSLATE works in practice and how to have the same behavior using REPLACE:

Result:

Observation: In the example above, I used TRANSLATE to remove special characters from a string. You can also do this using other options, like the ones I've shared in the articles below:

TRANSLATE with variables

In this other example, I want to show that it is possible to use TRANSLATE with variables. I will also show a difference in the behavior of TRANSLATE compared to REPLACE.

Result:

In the example above, it was very clear how TRANSLATE facilitates the writing and reading of code, avoiding typing errors, reducing complexity and facilitating maintenance and reading. But the 2 had the same result. And I wish it didn't have this spacing between characters, as in the print above.

Let's try to remove this.

At REPLACE it was pretty easy. Just change the space character ” ” by an empty string “”:

Result:

Now I'm going to try using TRANSLATE:

Result:

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

That is, I can't do the same thing with TRANSLATE in this case, because the length of the string to be found and the string to be replaced must be the same length. As much as I'm using REPLICATE to force the length to be equal, as the string is empty (“”), the number of characters doesn't increase.

This example then justifies the use of REPLACE in certain scenarios when you want to replace a character with an empty string, right?

Calm down.. We can try a "workaround" to solve this, merging a TRANSLATE with a single REPLACE, where I'll replace all the special characters by the null character (character 0) using TRANSLATE and then I'll replace all nulls by an empty string with a single REPLACE:

Result:

TRANSLATE + SELECT on tables

The TRANSLATE command also works normally if applied to datasets such as tables and views:

Result:

In the example above, I used TRANSLATE to remove numbers from a string. You can also do this using other options, like the ones I've shared in the articles below:

TRANSLATE in versions prior to 2017

As I mention at the beginning of the post, the TRANSLATE function is only available starting with SQL Server 2017, so if you need to use it in earlier versions, you'll have to make do with lots of nested REPLACE commands, right? NOT!!

I will share with you this function I found in this post here from StackOverflow and I made a few modifications so I don't need to create external tables.

Usage is the same as the original function:

Result:

Important: In general, user functions (UDF) can cause big performance problems, especially if used in the WHERE/JOIN clause and in very large tables. To learn more about this, read my post. SQL Server - Using Calculated Columns (or Computed Columns) for Performance Tuning.

Another tip: In general, user functions (UDF) are slow to run on large tables. Another alternative to greatly improve the performance of functions is to use SQLCLR functions, as I demonstrate the gains in the article SQL Server - Performance Comparison between Scalar Function and CLR Scalar Function.

Fabiano Amorim's video about TRANSLATE

The video that inspired me to write this article was one by the master Fabiano Amorim, the biggest national SQL Server myth (and one of the biggest references in the world)

And that's it folks!
A big hug and see you next time.

References