TRANSLATE() Function in SQL Server

TRANSLATE() function :
This function in SQL Server is used to return the translated string of the string stated in the first argument of this function, when the characters stated in the characters argument of the above function are converted into the characters stated in the last argument i.e, translations.

Features :

This function is used to find a modified string of the string stated in the first argument, when the characters given in the characters argument are converted into the characters given in the last argument i.e, translations.
This function accepts string, characters, and translations as parameter.
This function can translate the string fully as well as partially.
This function can return an error if specified characters and translations are not of same length.

Syntax :

TRANSLATE(string, characters, translations)

Parameter :
This method accepts three parameters as given below :

string : Specified input string which is to be translated.
characters : Specified characters which must be substituted.
translations : Specified new characters.

Returns :
It returns the modified string of the string stated in the first argument of this function, when the characters given in the characters argument are interpreted into the characters stated in the last argument i.e, translations.

Example-1 :
Getting a string from the specified string, characters and translations.

SELECT TRANSLATE('Geek', 'Geek', 'geek');

Output :

geek

Example-2 :
Using TRANSLATE() function with a variable and getting the translated string as output.

DECLARE @str VARCHAR(2);
SET @str = 'gf';
SELECT TRANSLATE(@str, 'gf', 'cs');

Output :

cs

Example-3 :
Using TRANSLATE() function with three variables and getting the translated string as output.

DECLARE @str VARCHAR(3);
DECLARE @chars VARCHAR(3);
DECLARE @newchar VARCHAR(3);
SET @str = 'abc';
SET @chars = 'ab';
SET @newchar = 'ed';
SELECT TRANSLATE(@str, @chars, @newchar);

Output :

edc

Example-4 :
Getting a translated string of the string stated in the first argument, when the characters given in the characters argument of this function are interpreted into the characters given in the last argument i.e, translations.

SELECT TRANSLATE('x*[y+z]/[x-y]', '[][]', '()()');

Output :

x*(y+z)/(x-y)

Application :
This function is used to return the translated string of the string stated in the first argument, when the characters given in the characters argument are interpreted into the characters given in the last argument i.e, translations.

Article Tags :

SQL

DBMS-SQL

SQL-Server