The Curious Case of the Nested REPLACE()
By October 6, 2019
onI 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.
DECLARE @Token1 NVARCHAR(MAX) = Replicate('d',5000) DECLARE @Token2 NVARCHAR(MAX) = Replicate('o',5000) DECLARE @Token3 NVARCHAR(MAX) = Replicate('h',5000) DECLARE @SQL NVARCHAR(MAX) Nested: BEGIN TRY SET @SQL = REPLACE(REPLACE(REPLACE(N' {Token1}{Token2}{Token3} ' ,'{Token1}',@Token1) ,'{Token2}',@Token2) ,'{Token3}',@Token3) SELECT @SQL END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumber ,ERROR_MESSAGE() AS ErrorMessage; GOTO Unnested END CATCH Unnested: BEGIN TRY SET @SQL = N' {Token1}{Token2}{Token3} ' SET @SQL = REPLACE(@SQL,'{Token1}',@Token1) SET @SQL = REPLACE(@SQL,'{Token2}',@Token2) SET @SQL = REPLACE(@SQL,'{Token3}',@Token3) SELECT @SQL END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumber ,ERROR_MESSAGE() AS ErrorMessage; END CATCH