SQL Server TRANSLATE Function

Summary: in this tutorial, you will learn how to use the SQL Server TRANSLATE() function to replace several single-characters, one-to-one translation in one operation.

SQL Server TRANSLATE() function overview

The TRANSLATE() function returns a string where character specified in the second argument are replaced with the matching characters from the third argument.

The following illustrates the syntax of the TRANSLATE() function:

TRANSLATE(input_string, from_characters, to_characters);
Code language: SQL (Structured Query Language) (sql)

In this syntax:

• input_string is a string to be searched. It can be a literal string, character expression, or column.
• from_characters is a string expression which contains the characters that should be replaced.
• to_characters is a string expression that contains the replacement characters.

If the lengths of from_characters and to_characters are different, the TRANSLATE() function will return an error.

If any argument is NULL, the TRANSLATE() function will return NULL.

SQL Server TRANSLATE() function examples

The following example uses the TRANSLATE() function to replace square braces with parentheses:

SELECT 
    TRANSLATE('[408] 555 6789','[]','()') result;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

result
--------------
(408) 555 6789

The statement is equivalent to call the REPLACE() function twice:

SELECT 
    REPLACE(REPLACE('[408] 555 6789','[','('),']',')') result;
Code language: SQL (Structured Query Language) (sql)

TRANSLATE() vs. REPLACE()

The behavior of the TRANSLATE() function is similar to calling multiple REPLACE() functions.

However, the TRANSLATE() function does not replace all occurrences of a character with a new one. This is the difference between the TRANSLATE() function and calling multiple REPLACE() functions, each REPLACE() function call would replace all relevant characters.

In this tutorial, you have learned how to use the SQL Server TRANSLATE() function to replace several single-character, one-to-one translation in one operation.