TRANSLATE Function in SQL Server

In this SQL Server tutorial, you will learn the TRANSLATE function in SQL Server, which helps replace the substring with a new string.

Then, you will understand the ‘What exactly is the TRANSLATE () function?’ with syntax; after that, you will do a simple example to understand the workings of the TRANSLATE() function.

Using an example, you will also learn the difference between the TRANSLATE() and REPLACE() functions and how to work with strings containing special characters.

Table of Contents

TRANSLATE Function in SQL Server

The TRANSLATE function in SQL Server replaces the individual characters in a string with a specified string (call it translation).

Just consider the TRANSLATE() function works like finding and replacing the string. In reality, it doesn’t replace the string but instead replaces (substituting) the characters in the string with a specified translation, which is a substring containing characters.

The syntax is given below.

TRANSLATE ( inputString, characters, translations )

Where,

TRANSLATE(): It is the function which translates the strings.
inputString: It is the source string where translation (replacement) occurs, or it can be an expression of the type varchar, varchar, char, char.
characters: It is the substring containing characters in a source string that you want to replace with another character.
translations: It is the string that contains characters that are substituted in place of the characters in the string; its length and datatype should be the same as the characters in the string you want to replace.

In simple words, the TRANSLATE () function accepts three values: inputString, characters, and translations. So, within inputString, characters are replaced by the translation (a string containing characters).

Let’s take an example to understand how the TRANSLATE() function works. For example, you have input string like ‘I get up at 6 AM and reach the office at 8 AM’; if you want to replace characters such as ‘6’ and ‘8’, look at the query below.

SELECT 
TRANSLATE('I get up at 6 AM and reach the office at 8 AM.','68', '77');

As you can see, characters 6 and 8 are replaced by characters 79, and a new string is returned, which is ‘I get up at 7 AM and reach the office at 9 AM’. This is how the TRANSLATE() function works in SQL Server.

TRANSLATE Function in SQL Server with Special Characters

You can handle special characters using the TRANSLATE() function; it lets you have a string equation like this: ‘4*[1+3]/{10-4}’ and want to substitute parenthesis in places of brackets and curly braces.

For that, use the query below.

SELECT 
TRANSLATE('4*[1+3]/{10-4}','{}[]', '()()') AS SubtituteSpecialChar;

TRANSLATE Function in SQL Server with Special Characters

From the output of the above query, the TRANSLATE() function substituted the parenthesis () in place of brackets [] and curly braces. You can see in the output it returns the string as 4*(1+3)/(10-4).

Look how the TRANSLATE function in SQL Server replaces the special characters. But here, I want to explain what happens if you’re going to do the same thing with the REPLACE() function.

The REPLACE() function also does the same thing, but you will need to use the multiple REPLACE() function to get the output as the TRANSLATE() has done in the above query.

For example, look at the query below, which does the replacement of special characters but uses the REPLACE() function.

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

REPLACE and TRANSLATE Function in SQL Server with Special Characters

As you can see in the above output, the REPLACE() function returns the same result as TRANSLATE() but by using one REPLACE() function within another.

This is the one difference you can see here. In the TRANSLATE function in SQL Server, you need to specify the characters you want to replace, the translation, and that’s it. If you have REPLACE(), you must nest the REPLACE() function.

Conclusion

In this SQL Server tutorial, you have applied the TRANSLATE function in SQL Server string literal and replaced some characters of that string with new characters. You also learned the difference between TRANSLATE() and REPLACE() functions, for example, finally replacing the special character with another special character.