Translate & Replace functions in SQL Server

This article introduces the new TRANSLATE and REPLACE string functions in SQL Server look identical, shifting letters from one to the other. There is a major difference between them, however.

Let’s see how close these functions are:

SELECT REPLACE (‘Flat_720’, ‘_’, ‘#’);
SELECT TRANSLATE (‘Flat_720′,’_’,’#’);

Both returns “Flat#720′

Let’s see what documentation says about these functions.

The purpose of the ‘REPLACE’ function is that it returns the initial argument with all occurrences of the target string replaced by the replacement string.

Let’s dig into more.

This is equivalent to the REPLACE function that is available in most of the RDBMS platforms (MySQL, Oracle & SQL Server). The matching is case-sensitive. If any argument is NULL, the return value is NULL.

SELECT REPLACE(‘MySQL is a free software’, ‘MySQL’, ‘Oracle MySQL’);

Oracle MySQL is a free software

SELECT REPLACE(‘obrocodobro’,’o’,’a’);


If no match found, the original string is returned unchanged.

SELECT REPLACE(‘SQL Bank’, ‘Code’,’Repository’);

‘SQL Bank’

TRANSLATE: Returns the input string with each character in the from argument replaced with the corresponding character in the to argument. The characters are matched in the order they appear in from and to.

SELECT TRANSLATE (‘hello world’,’world’,’earth’);

hetta earth

It translates letter by letter. If you look at the above example, the word “world” is replaced by “earth”. In addition, it replaced the fourth letter ‘L’ from the string “world” to the fourth letter “T” from the word “earth”. The change is applied to the entire string rather than just the input string. Similarly, the second letter ‘o’ is replaced from “world” to the second letter ‘a’ from ‘earth’. With this example, it is obvious that we cannot use ‘TRANSLATE’ instead of ‘REPLACE.’

Where we use ‘TRANSLATE’ then? Let’s see.
SELECT TRANSLATE(‘3*[2+1]/{8-4}’, ‘[]{}’, ‘()()’);


Hope you liked this post.

Please click on the follow button to receive updates on the latest posts.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s