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

  • 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

     


    Thursday, December 22, 2011 7:22 PM

Answers

All replies