The Curious Case of the Nested REPLACE()

By on October 6, 2019


I was writing some dynamic T-SQL earlier to build out some table updates on the fly when I came across a situation where it was failing due to the dreaded ‘String or binary data would be truncated.’

This only occurred when I was attempting to replace multiple areas with long pieces of text. After playing around with it I discovered that I was able to manually build the string fine. That made me dig a little deeper since the end result would fit within NVARCHAR(MAX).

I discovered that nested REPLACE() statements appear to mess with the datatype as they work. My guess is that it deals with underlying implicit conversion to NVARCHAR(4000).

The good news is that this does not seem to be the case when doing a sequence of single REPLACE() statements as those were able to successfully build the string with no issue.

Code to reproduce the issue is below. If you see something I am missing or have a proper explanation please feel free to let me know!

If you encounter a similar issue hopefully this will help you too.





Categorised in: ,

Leave a Reply

Your email address will not be published. Required fields are marked *


Want occasional notes from me?
(I promise, no spam.)

* indicates required

© Copyright 2020 The Data You Need. All rights reserved. Privacy Policy Cookie Policy