How To Use TRANSLATE Function In SQL Server

TRANSLATE, a string manipulation function, to substitute set of characters in a string with another set of characters. In the background, it works on a SINGLE CHARACTER substitution approach. Taking a single character specified in a string and replacing it with another set of character. Furthermore, advancing to the next character and so forth.

For the most part of data analysis and cleaning work, TRANSLATE function can significantly help. We can TRANSLATE a series of special characters, using ONE by ONE substitution approach with other characters.

In comparison to writing nested and complex REPLACE function. TRANSLATE can achieve the desired results using a single line of code.

TRANSLATE function was added with SQL Server 2017 edition, giving more ease to database developers to perform string manipulations by writing concise code. Besides, the ORACLE database supported this function since the initial release and working similarly as SQL Server.

Syntax

TRANSLATE (@InputString, @FromString, @ToString)

Arguments

@InputString - String where the function is applied on, to translate a specified set of characters to another.
@FromString - Character(s) to be searched within @InputString.
@ToString - Replacement character(s).

Important To Understand

@InputString is a source string where TRANSLATE function is applied.

@FromString parameter includes a series of character(s) to search within @InputString and replaced by a series of characters specified in @ToString.

Thus, it will take the first character specified in @FromString and substitute it with the first character mentioned in @ToString. Furthermore, the second character in @FromString substituted with the second character in @ToString and so forth.

TRANSLATE Function In SQL Server – Points To Ponder

1.ONE by ONE character substitution.

2.Works on SINGLE character substitution instead of words like REPLACE function.

3.Single character substitution is similar in TRANSLATE and REPLACE Function.

4.The 2nd and 3rd argument in this function should have an equal number of characters, not having so will result in an error.

5.Works only with SQL Server 2017 and future editions, and compatibility level must be 140 and above.

Examples

Let’s demonstrate TRANSLATE function with helpful examples. Before running these queries, be sure that you are on SQL Server 2017 version with compatibility level 140 or higher.

1.

TRANSLATE – Replace Numbers in String with Characters

In the following query, we can see that translate function takes ‘abc’ and replaces it with ‘123’ character by character. Replacing a with 1, b with 2 and c with 3.

SELECT 'a111b222c333' as BeforeTranslate, 
       TRANSLATE('a111b222c333','abc','123') as AfterTranslate;

Equivalent Nested REPLACE Function

SELECT 
  'a111b222c333' as BeforeReplace, 
   REPLACE(
		  REPLACE(
				  REPLACE('a111b222c333', 
				          'a', 1
			                 ), 
			  'b','2'
		         ), 
	    'c',3
          ) AfterReplace;

2.

TRANSLATE – Replace Special Characters

Following query demonstrates replacing special characters with another set of characters. Substitution process follows ONE by ONE character replacement. Achieving the following results using replace function requires writing nested replace therefore increasing complexity.

DECLARE @InputString as VARCHAR(50) = '[#91]7777,7777,77';

SELECT @InputString as BeforeTranslate, 
       TRANSLATE(@InputString,'[#],','(+)-') as AfterTranslate;

3.

REPLACE & TRANSLATE – Single Character Substitution

We can notice that the following query using replace and translate function – produces the same result because for the single character both the function follows the same approach.

SELECT 'a' as BeforeReplace, REPLACE('a','a','z') as AfterReplace,
       'a' as BeforeTranslate, TRANSLATE('a','a','z') as AfterTranslate;

4.

TRANSLATE – Equal Number of Characters in Argument

In particular, this function should have an equal number of characters in the second and third argument. Not having so will result in an error as below.

Msg 9828, Level 16, State 1, Line 1
The second and third arguments of the TRANSLATE built-in function must contain an equal number of characters.

Here we are trying to substitute ‘+’ (single char) with ‘- -’ (two chars) resulting in an error.

SELECT TRANSLATE('tech+recipes','+','--');

Summary

In summary, we have learned how to use TRANSLATE function introduced with SQL Server 2017 with helpful examples. How it can help us to avoid writing Nested REPLACE function. It can be widly used in data analysis and cleaning work.