By: Daniel Calbimonte
The TRANSLATE function converts characters in a string from one value to another value. This was introduced with SQL Server 2017.
Syntax
TRANSLATE(expression, charactersToChange, charactersTranslated)
Parameters
- expression - this is the string of characters to be changed.
- charactersToChange - this is the list of characters to change.
- charactersTranslated - this is the list of characters that will replace the characters to be changed.
Simple TRANSLATE Example
The following example will convert vowels to and "*". In the example, we have 5 vowels mapped to 5 "*" for the conversion.
SELECT TRANSLATE('What a wonderful world', 'aeiou', '*****') as message
The next example will remove the vowels and then REPLACE the "*" with an empty string.
SELECT REPLACE(TRANSLATE('What a wonderful world', 'aeiou', '*****'),'*','' )as message
TRANSLATE Comma to Decimal Point
In this example, we will replace the decimal point with a comma and comma with a decimal point.
SELECT TRANSLATE('2,345.345', '.,', ',.') as message
NULL Arguments with TRANSLATE Function
If the argument is NULL, the function will return a NULL value.
SELECT TRANSLATE(NULL, '.,', ',.') as message
TRANSLATE Function Not Recognized Error
TRANSLATE was implemented in SQL Server 2017. If your SQL Server is a lower version, you would need to upgrade to SQL Server 2017 or later to use this function.
The following link will help to find your SQL Server version: How to tell what SQL Server versions you are running.
TRANSLATE Function Equal Number of Characters Error
The characters to change should equal the translated characters, otherwise the function will return an error.
SELECT TRANSLATE('Hello world','aeiou,','AEIO') as message
The second and third arguments of the TRANSLATE built-in function must contain an equal number of characters. In order to fix this problem, we will add one extra character.
SELECT TRANSLATE('Hello world','aeiou,','AEIOU') as message
TRANSLATE Function Example with Table Column
The following example with replace parenthesis to square brackets in the table PersonPhone.
SELECT PhoneNumber, TRANSLATE(PhoneNumber,'()','[]') as modified FROM [Person].[PersonPhone]
