{"id":111898,"date":"2026-08-14T12:00:00","date_gmt":"2026-08-14T12:00:00","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=111898"},"modified":"2026-08-03T15:54:53","modified_gmt":"2026-08-03T15:54:53","slug":"how-to-avoid-nested-replace-functions-with-translate-in-sql-server","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/how-to-avoid-nested-replace-functions-with-translate-in-sql-server\/","title":{"rendered":"How to avoid nested REPLACE functions with TRANSLATE in SQL Server"},"content":{"rendered":"\n

If your T-SQL is buried under layers of nested REPLACE()<\/code> calls just to swap out a few characters, there’s a simpler way. SQL Server’s TRANSLATE()<\/code> function \u2014 introduced in 2017 but still rarely used \u2014 lets you replace multiple characters in a single string with one function call instead of stacking several REPLACE()<\/code> functions inside each other. <\/strong><\/p>\n\n\n\n

This guide covers how TRANSLATE()<\/code> works, how it compares to REPLACE()<\/code>, and where it falls short compared to other SQL dialects like PostgreSQL and Oracle.<\/strong><\/p>\n\n\n\n

Anyone who has worked with SQL Server<\/a> and T-SQL<\/a> will have seen code where REPLACE<\/a><\/code> functions have been nested, sometimes endlessly. The code ends up hard to read, hard to understand and, worse still, easy to make mistakes with during maintenance. It’s often a mess – and in many cases, a mess that can be avoided.<\/p>\n\n\n\n

That’s where the TRANSLATE<\/a><\/code> function – introduced to SQL Server back in 2017 – comes in. Nearly a decade on from its introduction, I rarely see the function used in code, but it’s excellent for this use case.<\/p>\n\n\n\n

What is the SQL Server TRANSLATE function?<\/h2>\n\n\n\n

Imagine I have incoming data with phone numbers in the following format:<\/p>\n\n\n\n

[02] 9232:4232<\/code><\/p>\n\n\n\n

In Australia, I’d like to see those phone numbers formatted like this:<\/p>\n\n\n\n

(02) 9232-4232<\/code><\/p>\n\n\n\n

That seems simple enough, but note the code required if I use the REPLACE<\/code> function:<\/p>\n\n\n\n

DECLARE @PhoneNumber varchar(20) = '[02] 9232:4232';\n\nSELECT REPLACE\n       (\n           REPLACE\n           (\n               REPLACE(@PhoneNumber, '[', '('), \n               ']', ')'\n           ), \n           ':', '-'\n       );<\/pre><\/div>\n\n\n\n
If it was just one or two replacements, I’d put it all on a single line. Then, as the number of replacements increases, I’ll start to spread it out over a series of lines. This is to try<\/em> to keep the parentheses matching – and to have some hope of following the code.<\/p>\n\n\n\n
The TRANSLATE()<\/code> function takes three parameters: an input string, a set of characters that should be replaced, and a set of characters to replace them. Here’s how we can use it for the example above:<\/p>\n\n\n\n
DECLARE @PhoneNumber varchar(20) = '[02] 9232:4232';\n\nSELECT TRANSLATE(@PhoneNumber, '[]:', '()-');<\/pre><\/div>\n\n\n\n
The characters being matched and replaced are determined by their position in the string. <\/p>\n\n\n\n
That’s not all, though. You might now wonder what happens if the characters are matched multiple times. For example…<\/p>\n\n\n\n
DECLARE @PhoneNumber varchar(20) = '[[02]] 9232:4232';\n\nSELECT TRANSLATE(@PhoneNumber, '[]:', '()-');\n<\/pre><\/div>\n\n\n\n
…will return ((02)) 9232-4232<\/code>.<\/p>\n\n\n\n
REPLACE<\/code> vs remove in SQL Server<\/h2>\n\n\n\n
Another reason why I often ended up using nested REPLACE<\/code> functions in SQL Server was to remove a particular set of characters out of a string<\/a>.<\/p>\n\n\n\n
Perhaps I want to replace the square brackets with round ones – but also want to remove the colon, not replace it. Unfortunately, the SQL Server implementation of the TRANSLATE<\/code> function doesn’t support this – as much as I wish it did!<\/p>\n\n\n\n
Here’s a good example of why this functionality is needed. If I execute this query…<\/p>\n\n\n\n
DECLARE @PhoneNumber varchar(20) = '[[02]] 9232:4232';\n\nSELECT TRANSLATE(@PhoneNumber, '[]:', '()');<\/pre><\/div>\n\n\n\n
…the following error is returned:<\/p>\n\n\n\n
Msg 9828, Level 16, State 1, Line 3
The second and third arguments of the TRANSLATE built-in function must contain an equal number of characters.<\/code><\/p>\n\n\n\n
In T-SQL, the string of matching characters and the string of replacement characters must<\/em> be the same length. That’s not the case in other SQL dialects. For more on this, see the official T-SQL documentation<\/a> (where they are called translations<\/em>.)<\/p>\n\n\n\n
In PostgreSQL<\/a>, Snowflake<\/a>, Databricks\/Apache Spark<\/a> SQL, Amazon Redshift<\/a>, and InterSystems IRIS<\/a>, if the replacement string is shorter than the matching string, the extra characters are removed. You can even have an empty string for the replacement and just use TRANSLATE<\/code> to remove a set of characters.<\/p>\n\n\n\n
Oracle<\/a> also allows the shorter replacement string, but you can’t have an empty string for the replacement. That’s treated as NULL<\/code> so, if you do that, the function returns NULL<\/code> instead. In my experience, people tend to put a ‘dummy’ matching character first, then use the same character as a replacement.<\/p>\n\n\n\n
I do wish SQL Server implemented the TRANSLATE<\/code> function better, but at least it has the function in the first place – unlike Google BigQuery<\/a> and MySQL<\/a>.<\/p>\n\n\n\n
Summary<\/h2>\n\n\n\n
If you’re still writing lots of nested REPLACE<\/code> functions in SQL Server, it’s time to see if any of them could be replaced (pun intended) by using the little-known TRANSLATE<\/code> function instead. It’s great for this use case. <\/p>\n\n\n\n
All I’d really like to see now is the SQL Server team remove the restriction on requiring the characters and the translations parameters that force them to be the same length.<\/p>\n\n\n\n
\n    
\n        
\n            
\n                
Fast, reliable and consistent SQL Server development…<\/h3>\n                
\n                                            …with SQL Toolbelt Essentials. 10 ingeniously simple tools for accelerating development, reducing risk, and standardizing workflows.                                    <\/div>\n            <\/div>\n                                            Learn more & try for free<\/a>\n                    <\/div>\n    <\/div>\n<\/section>\n\n\n
\n    
FAQs: Using TRANSLATE instead of REPLACE in SQL Server<\/h2>\n\n                        
1. What does the TRANSLATE function do in SQL Server?<\/h3>\n            
\n                
It replaces multiple single characters in a string with one function call, matching characters by position instead of nesting several REPLACE()<\/code> calls.<\/p>\n            <\/div>\n                    
2. How is TRANSLATE different from REPLACE?<\/h3>\n            
\n                
REPLACE()<\/code> handles one substring at a time and needs nesting for multiple swaps. TRANSLATE()<\/code> does several character replacements in a single, cleaner call.<\/p>\n            <\/div>\n                    
3. Can TRANSLATE remove characters instead of replacing them?<\/h3>\n            
\n                
Not in SQL Server \u2014 the character and replacement sets must be equal length, or you’ll get error 9828. PostgreSQL, Snowflake, and Oracle allow shorter replacement strings for removal.<\/p>\n            <\/div>\n                    
4. What happens if a character appears multiple times?<\/h3>\n            
\n                
Every occurrence is replaced. For example, [[02]] 9232:4232<\/code> with []:<\/code> \u2192 ()-<\/code> returns ((02)) 9232-4232<\/code>.<\/p>\n            <\/div>\n                    
5. Which databases support TRANSLATE?<\/h3>\n            
\n                
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
SQL Server, PostgreSQL, Snowflake, Databricks, Redshift, InterSystems IRIS, and Oracle. It’s missing from BigQuery and MySQL.<\/p>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n
\n
\n
\n
\n
\n
\u00a0<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n            <\/div>\n            <\/section>\n","protected":false},"excerpt":{"rendered":"
Learn how SQL Server’s TRANSLATE function replaces messy nested REPLACE statements. Simplify multi-character string replacements in T-SQL with one clean call.…<\/p>\n","protected":false},"author":346483,"featured_media":104555,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143523,53,143524,143531],"tags":[4168,4170,4150,4151,4252],"coauthors":[159368],"class_list":["post-111898","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-databases","category-featured","category-sql-server","category-t-sql-programming-sql-server","tag-database","tag-database-administration","tag-sql","tag-sql-server","tag-t-sql-programming"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/111898","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/346483"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=111898"}],"version-history":[{"count":5,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/111898\/revisions"}],"predecessor-version":[{"id":111991,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/111898\/revisions\/111991"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media\/104555"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=111898"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=111898"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=111898"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=111898"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}