the replace function with nvarchar(max) strings

Question

question

Henrik Staun Poulsen asked May 7, '10

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-2008 sql-server-2005 t-sql functions replace

10 |1200

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

Answer 1 · 2010-05-07T05:45:56Z

Matt Whitfield answered May 7, '10

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

10 |1200

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

Fatherjack ♦♦ commented · May 07, 2010 at 05:57 AM

+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 ·

question