The Curious Case of the Nested REPLACE()

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.

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

 

 

 

 

Categorised in: Blog, Blogs