REPLACE Function not accepting Search Strings (string_expression2) over 8K length

Question

I am getting errors using the REPLACE function (string_expression1, string_expression2 , string_expression3) when the string_expression2 is over 8 K long. BOL does not list any limitation on string lengths and the other two parameters can be over 8 K long using varchar(max) or nvarchar(max) datatypes. This issue was also raised in Wordpress and MSDN Forum articles (links below) .

Wordpress: http://phe1129.wordpress.com/2007/05/14/a-bug-of-replace-function-in-sql-server-2005-sp2/
MSDN Forum (SQL 2005): http://social.msdn.microsoft.com/Forums/en/transactsql/thread/d27d7ddf-7a5d-45b5-b2bc-3e8624fb5f7b

Script to replicate the issue

DECLARE @string  VARCHAR(MAX), @replacestring VARCHAR(MAX)
DECLARE @searchstring VARCHAR(MAX), @searchresult VARCHAR(MAX)

SET @string =  REPLICATE(cast('a' AS VARCHAR(max)), 20000)
SET @searchstring = REPLICATE(cast('a' AS VARCHAR(max)), 8000) --8000 works
SET @replacestring = REPLICATE(cast('b' AS VARCHAR(max)), 20000)

SELECT LEN(@string), LEN(@searchstring), LEN(@replacestring)

-- searched string is less than 8000 characters works
SELECT @searchresult = REPLACE(@string, @searchstring, @replacestring)
SELECT LEN(@searchresult), LEN(@searchstring), @replacestring

SET @string =  REPLICATE(cast('a' AS VARCHAR(max)), 20000)
SET @searchstring = REPLICATE(cast('a' AS VARCHAR(max)), 8001) -- DOES NOT WORK
SET @replacestring = REPLICATE(cast('b' AS VARCHAR(max)), 20000)

SELECT LEN(@string), LEN(@searchstring), LEN(@replacestring)

-- searched string is greater than 8000 characters, fails
SELECT @searchresult = REPLACE(@string, @searchstring, @replacestring)
SELECT LEN(@searchresult), LEN(@searchstring), @replacestring

 

Answer 1

I think I did read it before that replace function works with strings less than 8000 characters. However, since it's not documented, it is either documentation bug or it's a bug. I also found it documented on the Connect  http://connect.microsoft.com/SQLServer/feedback/details/277083/replace-t-sql-function-fails-if-the-searching-string-is-over-8000-bytes but I think it needs to at least make into Documentation.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 2

Thanks Naomi. It is odd that Replace function can handle 8K+ strings in ther other two parameters, but not on the search string.

The MSDN Social entry I was referring to is http://social.msdn.microsoft.com/Forums/en/transactsql/thread/d27d7ddf-7a5d-45b5-b2bc-3e8624fb5f7b. I corrected my original post.

Both MSDN Social and Microsoft Connect entries are for SQL 2005, and it seems like SQL 2008 also has the same issue.

Answer 3

Yes, I tested yesterday in SQL 2008 R2 and it indeed has the problem. Wondering if someone can test in Denali. The Connect item is closed with only few votes.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 4

You can use the .WRITE clause, check it here: http://sqlwithmanoj.wordpress.com/2011/10/04/update-statement-with-new-write-clause/

A simple example:

 

DECLARE @str NVARCHAR(MAX)

SET @str = REPLICATE(CAST(N'abcd' AS NVARCHAR(MAX)),1000) + REPLICATE(CAST(N'z' AS NVARCHAR(MAX)), 10)

SELECT len(@str), CHARINDEX('z', @str), SUBSTRING(@str, 4000, 4010)

SET @str.write(CAST(N'xxxxx' AS NVARCHAR(MAX)), 4000, 5)

SELECT len(@str), CHARINDEX('z', @str), SUBSTRING(@str, 4000, 4010)

 

---

~manoj | email: http://scr.im/m22g
http://sqlwithmanoj.wordpress.com
MCCA 2011