question

Henrik Staun Poulsen avatar image
Henrik Staun Poulsen asked

the replace function with nvarchar(max) strings

When I try to use the replace function on a string that is longer than 4000 char I get

Msg 8152, Level 16, State 10, Line 19 String or binary data would be truncated.

I think I have all variables as nvarchar(max), and I have tried Google, and various calls to CONVERT to no avail.

What am I doing wrong?

DECLARE @a NVARCHAR(MAX)
DECLARE @b nvarchar(max)

select @b = REPLICATE('a', 4000)
/*select @b = @b + '123412341234'*/
SET @a = N'TEst: #abc# :'
SET @a = REPLACE(@a , CONVERT(NVARCHAR(max),N'#abc#'), @b)
PRINT 'this line does not fail'
SET @a = CONVERT(NVARCHAR(max), REPLACE(@a , @b, CONVERT(NVARCHAR(max),N'#abc#')))


PRINT '------------------'

select @b = REPLICATE('a', 4000)
select @b = @b + '123412341234'
SET @a = N'TEst: #abc# :'
SET @a = REPLACE(@a , CONVERT(NVARCHAR(max),N'#abc#'), @b)
PRINT 'this line fails'
SET @a = CONVERT(NVARCHAR(max), REPLACE(@a , @b, CONVERT(NVARCHAR(max),N'#abc#')))
sql-server-2008sql-server-2005t-sqlfunctionsreplace
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Matt Whitfield avatar image
Matt Whitfield answered

From doing a bit of testing, it would appear that the second parameter to replace (the search expression) must be 4000 characters or less if the type is nvarchar and 8000 characters or less if the type is varchar.

This is either a bug, or a lack of documentation. I would suspect the latter, to be honest.

1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

+1 - a good way to clear up the bug list, just slap it in BOL and say it was meant to be like that!!!
3 Likes 3 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.