SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP

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)
SQL Translate Function Syntax

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 Translate Function Sample Code

SQL Server Translate function sample SQL code

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.

SQL string Translate function sample code

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

 REPLACE('Barbcbdbarb', 'ab', 'ba') as replace_out,
 TRANSLATE('Barbcbdbarb', 'ab', 'ba') as translate_out;
SQL Translate and Replace Function Comparison

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.

SQL Server Replace and Translate functions comparison

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.

 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;
Use Replace instead of SQL Translate Function

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.

Use Replace function instead of Translate on SQL Server

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.

SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019

Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.