SQL Translate Function Sample Code in SQL Server 2017

SQL Translate() function is introduced with SQL Server 2017 for database programmers. Transact-SQL translate functions enables SQL developer to replace a set of characters with their corresponding values in a given input string value. As SQL Server database developers will understand it is very similar to SQL Replace() functions, again we provide 3 input arguments. The first input parameter is the string which we want to change or modify by replacing some of its contained characters with new ones. The second input argument is the characters that we are going to replace with the characters given in the third and last input argument.

The Translate SQL function basic syntax is as follows:

TRANSLATE(inputString, charactersToReplace, newCharacters)

In SQL Replace() function the second argument is a single string to be replaced. Translate SQL function differs at this point. Each single character within second and third arguments are used in replacement one by one.

In fact if you have ABAP programming experience, you will see SQL TRANSLATE SQL function is very similar to ABAP TRANSLATE function.

Just one note, if any of the input argument is NULL then the result yields to NULL

SQL Translate() Function Samples

In this SQL Server 2017 T-SQL tutorial, SQL database programmers can find Translate function sample codes. I believe seeing the Translate() SQL function in action, will help database developers to understand how Translate() function can be used and its difference from Replace() SQL function.

Let's start our SQL tutorial with an ABRACADABRA !

SELECT TRANSLATE('Barbcbdbarb', 'ab', 'ba');

SQL programmers will see that there is no 'ab' within input string 'Barbcbdbarb'.
So REPLACE() function will not cause any change in this case.
But using REPLACE() function, SQL programmer will replace each 'a' (the first character in second argument) with 'b' (the first character in third argument).
And the 'b' which is the second character of second argument is replaced with the second character 'a' of the third argument.

Let's see what will be the return string if we execute SQL REPLACE function with same input variables

SELECT
 REPLACE('Barbcbdbarb', 'ab', 'ba') as replace_out,
 TRANSLATE('Barbcbdbarb', 'ab', 'ba') as translate_out;

The following result of the above SQL sample query will help database developers to visualize the difference between SQL Server Replace and Translate SQL string functions.

If your SQL Server platform is a previous version than SQL Server 2017, then the database developers can use multiple REPLACE functions in a cascaded form but with special case as follows.

SELECT
 TRANSLATE('Barbcbdbarb', 'ab', 'ba') as translate_out,
 REPLACE(REPLACE('Barbcbdbarb', 'a','b'), 'b','a') as failed_replace,
 REPLACE(REPLACE(REPLACE('Barbcbdbarb', 'a','|'), 'b','a'), '|','b') as replace_out;

In failed_replace column we see that the inner Replace function turned all a's to b's.
The outer Replace function this time replaces the resultant b's to a's which results more a's than expected.
The last column code first converts a's to a character '|' which we know that does not exist in input string.
Then at last step, we replace '|' characters to b's and reach the same desired output that SQL Server 2017 TRANSLATE function supplies.

It is worth to note that the Translate function replaces characters without case sensitivity. So lower or upper cases of the second argument characters are replaced with the exact character case (lower/upper) of the third argument characters.

For official documentation, database developers can visit the Microsoft Docs portal.