SQL Server TRANSLATE Function


By:

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
simple example of translate function

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 WITH REPLACE

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
TRANSLATE to modify numeric format

NULL Arguments with TRANSLATE Function

If the argument is NULL, the function will return a NULL value.

SELECT TRANSLATE(NULL, '.,', ',.') as message
TRANSLATE with NULL values

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.

TRANSLATE IS not a recognized built-in function name

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.

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
query results using translate function

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]
TRANSLATE change parenthesis to square brackets

Related Articles


Last Update: 11/11/2021




Comments For This Article





download














get free sql tips
agree to terms