A Bug of REPLACE Function in SQL Server 2005 SP2

/*

REPLACE function has the following syntax:

REPLACE ( string_expression1 , string_expression2 , string_expression3 )

string_expression1

Is the string expression to be searched. string_expression1 can be of a character or binary data type.

string_expression2

Is the substring to be found. string_expression2 can be of a character or binary data type.

string_expression3

Is the replacement string. string_expression3 can be of a character or binary data type.

If the string_expression2 is not more than 8000 bytes, it works fine. Try the following code:

*/

DECLARE @ToBeSearched nvarchar(max)

DECLARE @ToBeReplaced nvarchar(max)

DECLARE

@P1 int, @P2 int, @New nvarchar(max), @Tmp nvarchar(max)

SELECT

@P1=0,@P2=0,@Tmp=CAST(N‘A’ as nvarchar(max))

SET

@ToBeSearched=REPLICATE(@Tmp,200)+N‘Z’+REPLICATE(@Tmp,4000)+N‘Y’+REPLICATE(@Tmp,50)

SET

@P1=CHARINDEX(N‘Z’,@ToBeSearched,1)

SET

@P2=CHARINDEX(N‘Y’,@ToBeSearched,@P1)

SELECT

@P1,@P2,LEN(@ToBeSearched)

SET

@ToBeReplaced=SUBSTRING(@ToBeSearched,@P1+1,@P2@P11)

SELECT

LEN(@ToBeReplaced)

SELECT

@New=REPLACE(@ToBeSearched,@ToBeReplaced,N‘P’)

SELECT

LEN(@New)

PRINT

@New

GO

/*

If you change the @ToBeSearched from

SET @ToBeSearched=REPLICATE(@Tmp,200)+N’Z’+REPLICATE(@Tmp,4000)+N’Y’+REPLICATE(@Tmp,50)

to:

SET @ToBeSearched=REPLICATE(@Tmp,200)+N’Z’+REPLICATE(@Tmp,4001)+N’Y’+REPLICATE(@Tmp,50)

i.e., to be replaced string is 8002 bytes, you will get the following messages:

Msg 8152, Level 16, State 10, Line 14

String or binary data would be truncated.

This is a bug in the REPLACE function.

It works fine for string_expression3 to be more than 8000 bytes, e.g.:

*/

DECLARE

@ToBeSearched nvarchar(max)

DECLARE

@ReplaceWith nvarchar(max)

DECLARE

@New nvarchar(max)

SELECT @ReplaceWith=REPLICATE(CAST(N‘P’ as nvarchar(max)),4001)

SET @ToBeSearched=N‘AZSSYA’

SELECT

@New=REPLACE(@ToBeSearched,‘SS’,@ReplaceWith)

SELECT

LEN(@New)

PRINT

@New

GO

Advertisements
This entry was posted in SQL server 2005. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s