1

here is the code to re-produce the issue:

DECLARE @VBIN BINARY(50)
DECLARE @PASS NVARCHAR(3)
DECLARE @TEXT NVARCHAR(MAX)

SET @TEXT = '123456123789'
SET @PASS = '123'
SET @VBIN = CONVERT(BINARY, N'321')
SELECT REPLACE(@TEXT, @PASS,  CONVERT(NVARCHAR(MAX), @VBIN))

would return '321' insetead of '321456321789'

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
user2753829
  • 13
  • 2

1 Answers1

1

The issue is that binary(50) will pad with 0x00 to reach the fixed length of 50 and that is generally treated as a string terminator.

You see the same behaviour with

SELECT N'The quick brown ' +  NCHAR(0) + N' fox jumped over the lazy dog.'

The data is actually still there after the REPLACE. It is in the attempt to display it as a string that truncation occurs.

DECLARE @VBIN BINARY(50)
DECLARE @PASS NVARCHAR(3)
DECLARE @TEXT NVARCHAR(MAX)

SET @TEXT = '123456123789'
SET @PASS = '123'
SET @VBIN = CONVERT(BINARY, N'321')

SELECT REPLACE(@TEXT, @PASS,  CONVERT(NVARCHAR(MAX), @VBIN))
SELECT DATALENGTH(REPLACE(@TEXT, @PASS,  CONVERT(NVARCHAR(MAX), @VBIN))) /*112*/

SELECT CAST(REPLACE(@TEXT, @PASS,  CONVERT(NVARCHAR(MAX), @VBIN)) AS VARBINARY(112))

Using varbinary rather than binary would avoid the issue but I'm not sure what you are actually trying to do here anyway.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • thanks Martin, in your answer "SELECT N'The quick brown ' + NCHAR(0) + N' fox jumped over the lazy dog.'" led me to the right direction, for the solution i used a function from another post (http://stackoverflow.com/questions/15259622/how-do-i-remove-extended-ascii-characters-from-a-string-in-t-sql) to remove non ascii charachters form the string and now string does not terminates and i do get the desired output '321456321789' – user2753829 Sep 06 '13 at 11:52