TRANSLATE function is an alternative of REPLACE in SQL Server 2017

   

     SQL Server has the REPLACE function since 2008, to replace any characters in a string value. Let's look at the REPLACE function first to understand what can TRANSLATE improve in our queries.

REPLACE (expression, pattern, replacement)

expression is the string we like to change.

pattern is the sub string we like to find in expression

replacement is the string that will replace the characters found.

Here is an example.

   It is a simple function which the replaces found characters and It returns the new string with the replaced values. Replacement characters needs to exactly match for REPLACE to work. Let's say I want to replace square brackets with parentheses in a string, that should be an easy task with REPLACE right? Let's look at the example.

     This didn't work! REPLACE function expects pattern to match exactly to sub string of expression. This is a problem, and to fix this problem we need to use bunch of REPLACE functions.

     There it is, I need to use REPLACE function for each character I want replace. It works, but it is a nightmare. TRANSLATE function in SQL SERVER 2017 comes to our help in this situation.

TRANSLATE(source, looking_For, translations)

source is the string we like to change.

looking_For is the characters we like to replace.

translations is the characters which will replace the found characters in source

 Let's try to use TRANSLATE function in the same situation and see what TRANSLATE function is going to return.

     Now, I don't know about you but I like what I see here. Multiple REPLACE nightmares are over with TRANSLATE function. Only problem is, you need SQL SERVER 2017. This works great for strings, also it works great for Spatial data! You can convert GeoJSON data into WKT and vice-versa in this way.