MS SQL TRANSLATE

TRANSLATE is a new MS SQL Server function introduced from MS SQL Server 2017.

If you think that TRANSLATE has something in common with language translations than you are completely wrong 😊.

The function TRANSLATE replaces multiple characters inside the given string value.

Syntax:

TRANSLATE (input_string, characters_to_be_replaced, characters_to_replace_with)
-    input_string -   A string expression that needs to be changed
-    characters_to_be_replaced – Characters that should be replaced
-    characters_to_replace_with – Characters that will replace the characters to be replaced (2nd argument)

Example:

-- input value
DECLARE @StringValue nvarchar(max) = 'Replace :) with :(';
-- TRANSLATE replaces ) with (
SELECT TRANSLATE(@StringValue, ')', '(') AS Result;

The function returns a string expression of the same type (nvarchar, varchar...) as the input string (1st argument).

The function TRANSLATE cannot be used to replace a character with an empty string.

This was possible using the older REPLACE function in MS SQL server. This is also possible in C# using String.Replace(string, string).

If you try this, you will get an error:

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

Example:

-- input value
DECLARE @StringValue nvarchar(max) = 'Replace spaces with empty string.';
-- result of TRANSLATE
SELECT TRANSLATE(@StringValue, ' ', '') AS Result;

 And the old way:

-- input value
DECLARE @StringValue nvarchar(max) = 'Replace spaces with empty string.';
-- result of REPLACE
SELECT REPLACE(@StringValue, ' ', '') AS Result;

 

So, what’s the benefit of using TRANSLATE instead of good old REPLACE?

You can use TRANSLATE to replace multiple characters at once!

If you pass multiple characters in the 2nd argument they will be replaced by corresponding characters in the 3rd argument of the function. That’s the reason why you can’t replace a character with an empty string and the 2nd and the 3rd parameters must me of the same length.

REPLACE is able to handle only one replacement at the same time and can replace a char with an empty string. The benefit of TRANSLATE is that you can replace multiple characters (strings) at once.

Example:

-- input value
DECLARE @StringValue nvarchar(max) = 'Replace all brackets with parentheses in [database].[schema].[table_name].';
-- result of TRANSLATE
SELECT TRANSLATE(@StringValue, '[]', '()') AS Result
-- implementation of REPLACE
SELECT REPLACE(REPLACE(@StringValue,'[','('), ']', ')') AS Result;

-- input value
DECLARE @StringValue nvarchar(max) = 'Replace :( with ;)';
-- benefit of replace
SELECT REPLACE(@StringValue, ':(', ';)') AS Result;

*You can download the complete SQL Script with all examples from the post here: SQL Script.sql (1.23 kb)