Answered by:
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-3e8624fb5f7bScript 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- Edited by AshwinMahajan Friday, December 23, 2011 2:22 PM
Answers
-
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- Proposed as answer by Kent WaldropModerator Friday, December 23, 2011 3:07 PM
- Marked as answer by Kalman TothModerator Saturday, December 31, 2011 12:40 PM
-
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- Edited by Manoj Pandey (manub22)Microsoft employee Monday, December 26, 2011 10:46 AM
- Marked as answer by Kalman TothModerator Saturday, December 31, 2011 12:39 PM
All replies
-
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- Proposed as answer by Kent WaldropModerator Friday, December 23, 2011 3:07 PM
- Marked as answer by Kalman TothModerator Saturday, December 31, 2011 12:40 PM
-
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.
-
-
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- Edited by Manoj Pandey (manub22)Microsoft employee Monday, December 26, 2011 10:46 AM
- Marked as answer by Kalman TothModerator Saturday, December 31, 2011 12:39 PM