NEW T-SQL FEATURES IN SQL SERVER 2017- IV [ TRANSLATE() ]

In this post we shall check out the new features Microsoft introduced in SQL Server 2017 | TRANSLATE()

This function returns the string provided as a first argument after some characters specified in the second argument are translated into a destination set of characters.

Notes:-

1. This function is available in following SQL Versions
SQL Server (starting with 2017)
Azure SQL Database
Azure SQL Data Warehouse
Parallel Data Warehouse

2. The syntax of the function is
TRANSLATE ( inputString, characters, translations)

3. The function returns a character expression of the same type as inputString where characters from the second argument are replaced with the matching characters from third argument.

4. The TRANSLATE function will return an error if characters and translations have different lengths.

5. The TRANSLATE function should return unchanged input if null vales are provided as characters or replacement arguments.

6. The functioning of the TRANSLATE function is equivalent to using multiple REPLACE functions.

Example of TRANSLATE()

--

SELECT TRANSLATE('2*[3+4]/{7-2}', '[]{}', '()()');

--

OUTPUT

--

2*(3+4)/(7-2)

--

Note

The TRANSLATE function in this example is equivalent to but much simplier than the following statement using REPLACE:

--

SELECT REPLACE(REPLACE(REPLACE(REPLACE('2*[3+4]/{7-2}','[','('), ']', ')'), '{', '('), '}', ')');


--

Refer Microsoft LINK below for more details-

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com