TRANSLATE() is one among the new string function introduced in SQL Server 2017. This function avoids the usage of REPLACE() function multiple times to replace several different characters. For example if you want to replace [ and { to ( and } and ] to ) and _ to – in a string then you have to use REPLACE function five times. Using TRANSLATE() function in SQL Server 2017 is a simple, clean and straightforward way compared to REPLACE. Here is an example:
Using TRANSLATE()
-- TO replace [ and { with ( ; } and ] with ) ; _ with -
Declare @str as Varchar(50)
Set @str = '{Convert this} [and this] to _ something else'
-- In SQL Server 2016 and lower versions
Select REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@str, '{', '('), '[', '('), '}', ')'), ']', ')'), '_', '-')
-- IN SQL Server 2017 using the new TRANSLATE() function
Select TRANSLATE(@str, '{[]}_', '(())-')
/* Results */
-----------------------------------------------
(Convert this) (and this) to - something else
(1 row affected)
-----------------------------------------------
(Convert this) (and this) to - something else
(1 row affected)
This function is similar to the TRANSLATE function in Oracle/PLSQL. Microsoft took so long, but finally introduced in SQL Server 2017. The syntax of this function in SQL Server is TRANSLATE (inputString, characters, translations) where inputString is the string in which the characters needs to be replaced. Characters argument has one or more characters in the form of string which are to be replaced. Translations is the last argument and has the destination set of characters in string form.
There are other functions like CONCAT_WS() and TRIM() which are also newly introduced in SQL Server 2017.
Reference
- About TRANSLATE() function at Microsoft Docs.