SQL Server TRANSLATE() Function

 The TRANSLATE() function in SQL Server is used to replace a set of characters in a string with another set of characters. It's similar to the REPLACE() function, but while REPLACE() replaces one substring with another, TRANSLATE() works on individual characters and can replace multiple characters at once in a single operation.

Syntax:

TRANSLATE (input_string, from_string, to_string)

• input_string: The string on which the translation will be performed.
• from_string: A string containing the characters you want to replace.
• to_string: A string containing the characters to replace those in from_string.

The from_string and to_string must be of the same length. For each character in from_string, the corresponding character in to_string will replace it in input_string.

Example:

Example 1: Basic Use

SELECT TRANSLATE('abcdef', 'abc', '123') AS TranslatedString;

Result:

1 2 3 def

In this example, the characters 'a', 'b', and 'c' are replaced by '1', '2', and '3' respectively.

Example 2: Replacing Multiple Characters

SELECT TRANSLATE('hello world', 'ho', '01') AS TranslatedString;

Result:

10ell0 w01rld

In this example, 'h' is replaced with '1' and 'o' is replaced with '0'.

Example 3: Characters Not Found in from_string

If a character in input_string does not exist in from_string, it remains unchanged.

SELECT TRANSLATE('hello world', 'ho', '01') AS TranslatedString;

Result:

10ell0 w01rld

As shown, spaces and characters that are not listed in from_string remain unchanged.

Points to Remember:

• The length of from_string and to_string must match.
• Characters not found in from_string are not affected.
• TRANSLATE() works on a per-character basis.

This function is especially useful when you need to map a set of characters in a string to another set in one operation.