SQL TRANSLATE Function Use and Examples

Overview

The TRANSLATE function converts characters in a string from one value to another value. This was introduced with SQL Server 2017.

Explanation

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.

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

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]

Additional Information

• SQL REPLACE

Daniel Calbimonte

Daniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 10 years of experience as a QE and developer for SQL Server related software. He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs.

• MSSQLTips Awards: Author of the Year Contender – 2015-2018, 2022, 2023 | Champion (100+ tips) – 2018