{"id":1925,"date":"2018-05-12T22:21:12","date_gmt":"2018-05-13T05:21:12","guid":{"rendered":"https:\/\/database.guide\/?p=1925"},"modified":"2019-11-19T22:58:48","modified_gmt":"2019-11-20T05:58:48","slug":"sql-server-replace-vs-translate-what-are-the-differences","status":"publish","type":"post","link":"https:\/\/database.guide\/sql-server-replace-vs-translate-what-are-the-differences\/","title":{"rendered":"SQL Server REPLACE() vs TRANSLATE(): What are the Differences?"},"content":{"rendered":"

Starting with SQL Server 2017, you can now use the T-SQL<\/a> TRANSLATE()<\/code> function to translate one or more characters into another set of characters.<\/p>\n

At first glance, you might think that the\u00a0TRANSLATE()<\/code>\u00a0function does exactly the same thing as the\u00a0REPLACE()<\/code>\u00a0function, but there are significant differences between the two.<\/p>\n

<\/p>\n

Definitions<\/h2>\n

First, let’s look at the definition of each function:<\/p>\n

\n
REPLACE()<\/code><\/a><\/dt>\n
Replaces all occurrences of a specified string value with another string value.<\/dd>\n
TRANSLATE()<\/code><\/a><\/dt>\n
Returns the string provided as a first argument after some characters specified in the second argument are translated into a destination set of characters.<\/dd>\n<\/dl>\n

The main difference is how each function deals with multiple characters. REPLACE()<\/code>\u00a0replaces one string with another string. Therefore, if a string contains multiple characters, each character must be in the same order. TRANSLATE()<\/code>\u00a0on the other hand, replaces each character one by one, regardless of the order of those characters.<\/p>\n

Example – Same result<\/h2>\n

There are some cases where both functions will return the same result. Like this:<\/p>\n

SELECT \r\n    REPLACE('123', '123', '456') AS Replace,\r\n    TRANSLATE('123', '123', '456') AS Translate;\r\n<\/pre>\n
Result:<\/p>\n
Replace  Translate\r\n-------  ---------\r\n456      456      \r\n<\/pre>\n
In this case, REPLACE()<\/code> returns 456<\/code> because the exact string in the second argument matched a string in first argument (in this case, it was the whole string).<\/p>\n
TRANSLATE()<\/code> returns 456<\/code> because each character in the second argument is present in the first argument.<\/p>\n
Example – Different Result<\/h2>\n
Now for an example that demonstrates one of the differences between\u00a0TRANSLATE()<\/code> and\u00a0REPLACE()<\/code>:<\/p>\n
SELECT \r\n    REPLACE('123', '321', '456') AS Replace,\r\n    TRANSLATE('123', '321', '456') AS Translate;\r\n<\/pre>\n
Result:<\/p>\n
Replace  Translate\r\n-------  ---------\r\n123      654      \r\n<\/pre>\n
In this case, REPLACE()<\/code> has no effect (it returns the original string) because the second argument is not an exact match for the first argument (or a substring within it). Even though the second argument contains the correct characters, they are not in the same order as the first argument, and therefore, the whole string doesn’t match.<\/p>\n
TRANSLATE()<\/code> does<\/em> have an effect because each character in the second argument is present in the first argument. It doesn’t matter that they’re in a different order, because each character is translated one by one. SQL Server translates the first character, then the second, then the third, and so on.<\/p>\n
Non-Contiguous Strings<\/h2>\n
Similar to the previous example, you can also get different results when the first argument contains the characters in the second argument, but they are non-contiguous:<\/p>\n
SELECT \r\n    REPLACE('1car23', '123', '456') AS Replace,\r\n    TRANSLATE('1car23', '123', '456') AS Translate;\r\n<\/pre>\n
Result:<\/p>\n
Replace  Translate\r\n-------  ---------\r\n1car23   4car56   \r\n<\/pre>\n
Arguments of a Different Length<\/h2>\n
You may also get different results between each function whenever there are discrepancies in the number of characters in the various arguments.<\/p>\n
Here’s an example where the first argument contains less characters than the second and third arguments:<\/p>\n
SELECT \r\n    REPLACE('123', '1234', '4567') AS Replace,\r\n    TRANSLATE('123', '1234', '4567') AS Translate;\r\n<\/pre>\n
Result:<\/p>\n
Replace  Translate\r\n-------  ---------\r\n123      456      \r\n<\/pre>\n
In this case, REPLACE()<\/code> has no effect because the second argument contains more characters than the first argument. Therefore, it’s impossible for the first argument to contain the second argument.<\/p>\n
The TRANSLATE()<\/code> function however, does have an effect in this case. This is because the second argument contains characters that are in the first argument. It doesn’t matter that the second argument contains more characters than the first. The most important thing is that the third argument contains the same number of characters as the second.<\/p>\n
There are also cases when REPLACE()<\/code> works perfectly but TRANSLATE()<\/code> throws an error.<\/p>\n
Example:<\/p>\n
SELECT REPLACE('1234', '123', '4567') AS Replace;\r\n<\/pre>\n
Result:<\/p>\n
Replace\r\n-------\r\n45674  \r\n<\/pre>\n
In this case, REPLACE()<\/code> works as expected.<\/p>\n
However, if we provide the same arguments to TRANSLATE()<\/code>, we get an error:<\/p>\n
SELECT TRANSLATE('1234', '123', '4567') AS Translate;\r\n<\/pre>\n
Result:<\/p>\n
Error: The second and third arguments of the TRANSLATE built-in function must contain an equal number of characters. \r\n<\/pre>\n
As the error message states, the second and third arguments must contain an equal number of characters.<\/p>\n
When Should I Use REPLACE()?<\/h2>\n
You should use REPLACE()<\/code> when you need to replace all occurrences of a specific string, exactly as it is written. For example, changing someone’s name to another name.<\/p>\n
Using TRANSLATE()<\/code> in such cases can have disastrous results:<\/p>\n
SELECT \r\n    REPLACE('Homer Simpson', 'Homer', 'Jason') AS Replace,\r\n    TRANSLATE('Homer Simpson', 'Homer', 'Jason') AS Translate;<\/pre>\n
Result:<\/p>\n
Replace        Translate    \r\n-------------  -------------\r\nJason Simpson  Jason Sispsan\r\n<\/pre>\n
When Should I Use TRANSLATE()?<\/h2>\n
As demonstrated\u00a0 in the previous example, the TRANSLATE()<\/code> function can be useful if you need to replace all occurrences of each character specified, regardless of their order within the original string.<\/p>\n
It can also be used in place of REPLACE()<\/code> to simply the code. Here’s an example (based on an example<\/a> on the Microsoft website):<\/p>\n
SELECT \r\n    REPLACE(REPLACE(REPLACE(REPLACE('2*[3+4]\/{7-2}','[','('), ']', ')'), '{', '('), '}', ')') AS Replace,\r\n    TRANSLATE('2*[3+4]\/{7-2}', '[]{}', '()()') AS Translate;<\/pre>\n
Result:<\/p>\n
Replace        Translate    \r\n-------------  -------------\r\n2*(3+4)\/(7-2)  2*(3+4)\/(7-2)\r\n<\/pre>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
Starting with SQL Server 2017, you can now use the T-SQL TRANSLATE() function to translate one or more characters into another set of characters. At first glance, you might think that the\u00a0TRANSLATE()\u00a0function does exactly the same thing as the\u00a0REPLACE()\u00a0function, but there are significant differences between the two.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[7,5],"tags":[93,77,67,61],"class_list":["post-1925","post","type-post","status-publish","format-standard","hentry","category-sql","category-sql-server","tag-functions","tag-mssql","tag-string-functions","tag-t-sql"],"_links":{"self":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/1925","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/comments?post=1925"}],"version-history":[{"count":16,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/1925\/revisions"}],"predecessor-version":[{"id":5390,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/1925\/revisions\/5390"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=1925"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=1925"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=1925"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}