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
If no match found, the original string is returned unchanged.
SELECT REPLACE(‘SQL Bank’, ‘Code’,’Repository’);
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
SELECT TRANSLATE (‘hello world’,’world’,’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.’
Hope you liked this post.
Please click on the follow button to receive updates on the latest posts.