SQL Server TRANSLATE() Function

The TRANSLATE() function in SQL Server is a powerful string manipulation tool that allows you to replace specific characters within a string with other characters. It's particularly useful for standardizing text or converting between character sets.

TRANSLATE(): Definition and Usage

TRANSLATE() maps characters from one set to another. You provide the input string, the characters to be replaced, and the characters that will replace them. Importantly, the lengths of the "characters to replace" and "replacement characters" strings *must* be the same; otherwise, an error occurs.

Syntax


TRANSLATE(string, characters, translations)

Parameter Values

Parameter	Description
`string`	The input string. This is required.
`characters`	The characters to be replaced. This is required. Must be the same length as `translations`.
`translations`	The replacement characters. This is required. Must be the same length as `characters`.

Examples

Character Replacement

This example replaces the characters in "Monday" with those in "Sunday".

Syntax


SELECT TRANSLATE('Monday', 'Monday', 'Sunday');

Output


Sunday

Replacing Multiple Characters

Replacing multiple characters simultaneously using different mappings.

Syntax


SELECT TRANSLATE('3*[2+1]/{8-4}', '[]{}', '()()');

Output


3*(2+1)/(8-4)